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 80000007; asc ;; 1: len 6; hex 0000004eb50c; asc
N ;; 2: len 7; hex 0000008013285c; 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 80000007; asc ;; 1: len 6; hex 0000004eb50c; asc
N ;; 2: len 7; hex 0000008013285c; 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/mysql?unsub=gcdmg-mysql@m.gmane.org

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 80000006; asc ;; 1: len 6; hex 000000000510; asc
;; 2: len 7; hex 800000002d0110; asc - ;; 3: len 20; hex
6875616e676a7920202020202020202020202020; asc huangjy ;;




On 12/11/06, leo huang 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 80000007; asc ;; 1: len 6; hex 0000004eb50c; asc
> N ;; 2: len 7; hex 0000008013285c; 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 80000007; asc ;; 1: len 6; hex 0000004eb50c; asc
> N ;; 2: len 7; hex 0000008013285c; 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/mysql?unsub=eric.bergen@gmail.com
>
>


--
Eric Bergen
eric.bergen@provenscaling.com
http://www.provenscaling.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

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 80000007; asc ;; 1: len 6; hex 0000004eb50c; asc
N ;; 2: len 7; hex 0000008013285c; 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 80000007; asc ;; 1: len 6; hex 0000004eb50c; asc
N ;; 2: len 7; hex 0000008013285c; 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/mysql?unsub=gcdmg-mysql@m.gmane.org

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 :
> 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 80000007; asc ;; 1: len 6; hex 0000004eb50c; asc
> N ;; 2: len 7; hex 0000008013285c; 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 80000007; asc ;; 1: len 6; hex 0000004eb50c; asc
> N ;; 2: len 7; hex 0000008013285c; 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/mysql?unsub=leo.huang.list@gmail.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

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_GAP 512 /* 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/mysql?unsub=gcdmg-mysql@m.gmane.org