Hi Miguel ， I’ve tried your example “the affect gap locks to SELECT … FOR UPDATE ” on my server ,and my results are… affected (0.00 sec) root@localhost:test 14:35:29>select * from t; +——+ | i | +——+ | 21 | | 30 | | 26 | +——+ 3 rows in set… mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id 31929…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… execute SELECT * FROM id > 1000 FOR UPDATE twice, you expect to get the same value twice. To accomplish that, InnoDB locks all… with an exclusive lock and the gaps between them with a shared gap lock. This lock doesn’t only affect to SELECT … FOR UPDATE. This is an example with a DELETE statement: transaction1 > SELECT * FROM…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… Lock should not conflict with another Read Lock, it does for this statement, and as such it has to wait for any SELECT statement to complete in order to complete locking tables. This means if you have workload which includes some very long SELECT… and either kill FLUSH TABLES WITH READ LOCK and fail backup or kill long running SELECT queries to let backup to proceed…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…+———-+ | count(*) | +———-+ | 2359296 | +———-+ 1 row in set (1.73 sec) mysql> select * from C; +——+ | t | +——+ | test | +——+ 1 row in set (0…client = @saved_cs_client */; — – Dumping data for table `C` — LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; …
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…Even though with general lock compatibility guidelines Read Lock should not conflict with another Read Lock, it does for this statement, and as such it has to wait for any SELECT statement to complete in order to complete locking tables. The query state says “Waiting…
Comment: InnoDB's gap locks
April 4, 2012 By 14 Comments
… InnoDB doesn’t take additional locks on the gap, improving the concurrence. You would avoid all “Lock wait timeout exceeded” messages in my example. So only row with value 25 would be locked, not everything between 21 and 30, including the gap. Compare… sec) session1> commit; session2> commit; session1> select * from t; +——+ | i | +——+ | 21 | | 26 | | 30 | +——+ No locks on the gap, so no wait timeout…
Comment: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
March 11, 2012 By 7 Comments
… one row to your my.ini file. # =0 is traditional lock mode == > funcionality of auto_increment like before 5.1.22 # =1 is default value – consecutive lock mode # =2 is interleaved lock mode , i don’t investigate what it do… into tab (name) values (‘Peter’),(‘Oto’),(‘Jan’),(‘Jan’),(‘Jan’),(‘Romco’); select * from tab order by 1; 1 Peter 2 Oto 3…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row …cache 512619219 (509995888 + 2623331) File system 294352 (82672 + 211680) Lock system 318875832 (318747272 + 128560) Recovery system 0 (0 + 0…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 69 Comments
…_lock_wait_timeout = 50 A count command with approx 3 million records is taking too long and all my transactions are locked. select…
Comment: InnoDB's gap locks
April 6, 2012 By 14 Comments
… transactions will be REPEATABLE READ until the first write (or SELECT FOR UPDATE) happens, at which point the transaction is implicitly… DBA StackExchange: http://dba.stackexchange.com/questions/15854/innodb-row-locking-how-to-implement/15864#15864. I haven’t tested it… it should definitely behave the same if you change the SELECT FOR UPDATE into an UPDATE.
