Re: Why innodb can give the same X gap lock to two transactions?
Leo, Leo Huang wrote: Heikki, Thanks for you help! I also read the comment in file of innodbase/lock/lock0lock.c in which you said Different transaction can have conflicting locks set on the gap at the same time.. I think that the innodb gap lock's behavior just like an IX lock's behavior. hmm... yes, we could think that when a row is inserted, the inserted needs an 'X-lock on the whole gap'! Then the 'IX-locks' on the gap stop the insertion. When a transaction want to insert a record, it must also get the LOCK_INSERT_INTENTION of the gap, isn't it? Yes, but in the above analogy, an insert is really requesting an 'X lock on the whole gap'. The name LOCK_INSERT_INTENTION is then somewhat misleading. I have read some source code in innodbase/lock/lock0lock.c. But I can't get a clear view of innodb lock modes and lock ways? Can you give me more information? On tables, InnoDB has X, S, IX, IS, and AUTO-INC type locks. InnoDB has basically just X and S type locks on records and gaps. The complexity comes from this: lock0lock.h in 5.0: #define LOCK_ORDINARY 0 /* this flag denotes an ordinary next-key lock in contrast to LOCK_GAP or LOCK_REC_NOT_GAP */ #define LOCK_GAP512 /* this gap bit should be so high that it can be ORed to the other flags; when this bit is set, it means that the lock holds only on the gap before the record; for instance, an x-lock on the gap does not give permission to modify the record on which the bit is set; locks of this type are created when records are removed from the index chain of records */ #define LOCK_REC_NOT_GAP 1024 /* this bit means that the lock is only on the index record and does NOT block inserts to the gap before the index record; this is used in the case when we retrieve a record with a unique key, and is also used in locking plain SELECTs (not part of UPDATE or DELETE) when the user has set the READ COMMITTED isolation level */ #define LOCK_INSERT_INTENTION 2048 /* this bit is set when we place a waiting gap type record lock request in order to let an insert of an index record to wait until there are no conflicting locks by other transactions on the gap; note that this flag remains set when the waiting lock is granted, or if the lock is inherited to a neighboring record */ Unfortunately, the only existing documentation of the details of gap locking is in the source code and comments in lock0lock.c. PS: hi, Eric, Our MySQL version is 4.1.18. Thx! Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why innodb can give the same X gap lock to two transactions?
Leo, 'gap' locks in InnoDB are purely 'inhibitive': they block inserts to the locked gap. But they do not give the holder of the lock any right to insert. Several transactions can own X-lock on the same gap. The reason why we let 'conflicting' locks of different transactions on a gap is that this way there are less lock waits and less deadlocks. In Eric Bergen's example, there was a row with id 6, and there the locks were not gap locks. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php . From: leo huang Date: December 12 2006 7:46am Subject: Why innodb can give the same X gap lock to two transactions? Get Plain Text Hi, all, We have an innodb table named test. It has some rows as follow: mysql show create table test; +---+-+ | Table | Create Table | +---+-+ | test | CREATE TABLE `test` ( `id` int(11) NOT NULL default '0', `name` char(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+-+ 1 row in set (1.75 sec) mysql select * from test; ++-+ | id | name| ++-+ | 1 | huangjy | | 2 | huangjy | | 3 | huangjy | | 4 | huangjy | | 5 | huangjy | | 7 | huangjy | | 8 | huangjy | | 9 | huangjy | ++-+ 8 rows in set (1.98 sec) When I start two transactions as follow: Transaction 1: mysql begin; Query OK, 0 rows affected (2.51 sec) mysql select * from test where id=6 for update; Empty set (2.17 sec) Transaction 2: mysql begin; Query OK, 0 rows affected (1.56 sec) mysql select * from test where id=6 for update; Empty set (2.27 sec) Now, I use show engine innodb status to see the innodb lock status. The output as follow: TRANSACTIONS Trx id counter 0 5168907 Purge done for trx's n:o 0 5168898 undo n:o 0 0 History list length 2 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208 MySQL thread id 2, query id 46 localhost root show engine innodb status ---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread id 2484820912 2 lock struct(s), heap size 320 MySQL thread id 1, query id 45 localhost root TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168906 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ;; ---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread id 2484419504 2 lock struct(s), heap size 320 MySQL thread id 3, query id 43 localhost root TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168905 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ... As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both get the X gap locks on the same record. The MySQL Manual said that X lock is an exclusive lock. Why two transactions can get the same X lock? Any comment will be welcomed? Best regards, Leo Huang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why innodb can give the same X gap lock to two transactions?
Heikki, Thanks for you help! I also read the comment in file of innodbase/lock/lock0lock.c in which you said Different transaction can have conflicting locks set on the gap at the same time.. I think that the innodb gap lock's behavior just like an IX lock's behavior. When a transaction want to insert a record, it must also get the LOCK_INSERT_INTENTION of the gap, isn't it? I have read some source code in innodbase/lock/lock0lock.c. But I can't get a clear view of innodb lock modes and lock ways? Can you give me more information? PS: hi, Eric, Our MySQL version is 4.1.18. Thx! -- Best regards, Leo Huang 2006/12/18, Heikki Tuuri [EMAIL PROTECTED]: Leo, 'gap' locks in InnoDB are purely 'inhibitive': they block inserts to the locked gap. But they do not give the holder of the lock any right to insert. Several transactions can own X-lock on the same gap. The reason why we let 'conflicting' locks of different transactions on a gap is that this way there are less lock waits and less deadlocks. In Eric Bergen's example, there was a row with id 6, and there the locks were not gap locks. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php . From: leo huang Date: December 12 2006 7:46am Subject: Why innodb can give the same X gap lock to two transactions? Get Plain Text Hi, all, We have an innodb table named test. It has some rows as follow: mysql show create table test; +---+-+ | Table | Create Table | +---+-+ | test | CREATE TABLE `test` ( `id` int(11) NOT NULL default '0', `name` char(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+-+ 1 row in set (1.75 sec) mysql select * from test; ++-+ | id | name| ++-+ | 1 | huangjy | | 2 | huangjy | | 3 | huangjy | | 4 | huangjy | | 5 | huangjy | | 7 | huangjy | | 8 | huangjy | | 9 | huangjy | ++-+ 8 rows in set (1.98 sec) When I start two transactions as follow: Transaction 1: mysql begin; Query OK, 0 rows affected (2.51 sec) mysql select * from test where id=6 for update; Empty set (2.17 sec) Transaction 2: mysql begin; Query OK, 0 rows affected (1.56 sec) mysql select * from test where id=6 for update; Empty set (2.27 sec) Now, I use show engine innodb status to see the innodb lock status. The output as follow: TRANSACTIONS Trx id counter 0 5168907 Purge done for trx's n:o 0 5168898 undo n:o 0 0 History list length 2 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208 MySQL thread id 2, query id 46 localhost root show engine innodb status ---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread id 2484820912 2 lock struct(s), heap size 320 MySQL thread id 1, query id 45 localhost root TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168906 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ;; ---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread id 2484419504 2 lock struct(s), heap size 320 MySQL thread id 3, query id 43 localhost root TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168905 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ... As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both get the X gap locks on the same record. The MySQL Manual said that X lock is an exclusive lock. Why two transactions can get the same X lock? Any comment will be welcomed? Best regards, Leo Huang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql
Re: Why innodb can give the same X gap lock to two transactions?
Which version of mysql is this? In 5.1.12 when I run your test the section transaction blocks waiting for the lock (as it should). My show innodb status output is: TRANSACTIONS Trx id counter 0 1300 Purge done for trx's n:o 0 1288 undo n:o 0 0 History list length 1 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 1284, not started, process no 23890, OS thread id 1116363696 MySQL thread id 2, query id 25 localhost root ---TRANSACTION 0 1299, ACTIVE 21 sec, process no 23890, OS thread id 1116765104 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 3201 row lock(s) MySQL thread id 5, query id 58 localhost root statistics select * from test where id=6 for update Trx has approximately 1 row locks --- TRX HAS BEEN WAITING 21 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 52 n bits 80 index `PRIMARY` of table `test`.`test` trx id 0 1299 lock_mode X locks rec but not gap waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8006; asc ;; 1: len 6; hex 0510; asc ;; 2: len 7; hex 80002d0110; asc - ;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ;; On 12/11/06, leo huang [EMAIL PROTECTED] wrote: Hi, all, We have an innodb table named test. It has some rows as follow: mysql show create table test; +---+-+ | Table | Create Table | +---+-+ | test | CREATE TABLE `test` ( `id` int(11) NOT NULL default '0', `name` char(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+-+ 1 row in set (1.75 sec) mysql select * from test; ++-+ | id | name| ++-+ | 1 | huangjy | | 2 | huangjy | | 3 | huangjy | | 4 | huangjy | | 5 | huangjy | | 7 | huangjy | | 8 | huangjy | | 9 | huangjy | ++-+ 8 rows in set (1.98 sec) When I start two transactions as follow: Transaction 1: mysql begin; Query OK, 0 rows affected (2.51 sec) mysql select * from test where id=6 for update; Empty set (2.17 sec) Transaction 2: mysql begin; Query OK, 0 rows affected (1.56 sec) mysql select * from test where id=6 for update; Empty set (2.27 sec) Now, I use show engine innodb status to see the innodb lock status. The output as follow: TRANSACTIONS Trx id counter 0 5168907 Purge done for trx's n:o 0 5168898 undo n:o 0 0 History list length 2 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208 MySQL thread id 2, query id 46 localhost root show engine innodb status ---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread id 2484820912 2 lock struct(s), heap size 320 MySQL thread id 1, query id 45 localhost root TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168906 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ;; ---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread id 2484419504 2 lock struct(s), heap size 320 MySQL thread id 3, query id 43 localhost root TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168905 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ... As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both get the X gap locks on the same record. The MySQL Manual said that X lock is an exclusive lock. Why two transactions can get the same X lock? Any comment will be welcomed? Best regards, Leo Huang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.provenscaling.com --
Why innodb can give the same X gap lock to two transactions?
Hi, all, We have an innodb table named test. It has some rows as follow: mysql show create table test; +---+-+ | Table | Create Table | +---+-+ | test | CREATE TABLE `test` ( `id` int(11) NOT NULL default '0', `name` char(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+-+ 1 row in set (1.75 sec) mysql select * from test; ++-+ | id | name| ++-+ | 1 | huangjy | | 2 | huangjy | | 3 | huangjy | | 4 | huangjy | | 5 | huangjy | | 7 | huangjy | | 8 | huangjy | | 9 | huangjy | ++-+ 8 rows in set (1.98 sec) When I start two transactions as follow: Transaction 1: mysql begin; Query OK, 0 rows affected (2.51 sec) mysql select * from test where id=6 for update; Empty set (2.17 sec) Transaction 2: mysql begin; Query OK, 0 rows affected (1.56 sec) mysql select * from test where id=6 for update; Empty set (2.27 sec) Now, I use show engine innodb status to see the innodb lock status. The output as follow: TRANSACTIONS Trx id counter 0 5168907 Purge done for trx's n:o 0 5168898 undo n:o 0 0 History list length 2 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208 MySQL thread id 2, query id 46 localhost root show engine innodb status ---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread id 2484820912 2 lock struct(s), heap size 320 MySQL thread id 1, query id 45 localhost root TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168906 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ;; ---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread id 2484419504 2 lock struct(s), heap size 320 MySQL thread id 3, query id 43 localhost root TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168905 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ... As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both get the X gap locks on the same record. The MySQL Manual said that X lock is an exclusive lock. Why two transactions can get the same X lock? Any comment will be welcomed? Best regards, Leo Huang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
