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/[EMAIL PROTECTED]
