List:General Discussion« Previous MessageNext Message »
From:Eric Bergen Date:December 16 2006 11:16pm
Subject:Re: Why innodb can give the same X gap lock to two transactions?
View as plain text  
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 <leo.huang.list@stripped> 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=1
>
>


-- 
Eric Bergen
eric.bergen@stripped
http://www.provenscaling.com
Thread
Why innodb can give the same X gap lock to two transactions?leo huang12 Dec
  • Re: Why innodb can give the same X gap lock to two transactions?Eric Bergen17 Dec
Re: Why innodb can give the same X gap lock to two transactions?Heikki Tuuri18 Dec
  • Re: Why innodb can give the same X gap lock to two transactions?Leo Huang19 Dec
    • Re: Why innodb can give the same X gap lock to two transactions?Heikki Tuuri21 Dec