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]