MySQL Bugs: #6747: innodb_locks_unsafe_for_binlog still uses next-key locking

Bug #6747	innodb_locks_unsafe_for_binlog still uses next-key locking
Submitted:	22 Nov 2004 5:59	Modified:	30 Nov 2004 11:58
Reporter:	Yoshinori Matsunobu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.1.7	OS:	Linux (RedHat Enterprise Linux AS 3.0)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
MySQL manual says that "innodb_locks_unsafe_for_binlog  causes InnoDB not to use next-key locking in searches or index scans" , but it still seems to use next-key locking.

How to repeat:
mysql> show variables like '%innodb_locks_unsafe%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | ON    |
+--------------------------------+-------+

Transaction 1:
create table table2(col1 integer , index(col1)) engine=innodb;
insert into table2 values(100),(101),(102),(103),(104);
start transaction;
update table2 set col1=110 where col1=102;

Then execute Transaction 2:
start transaction;
insert into table2 values(102);
  -->waiting for Transaction 1 to end !

innodb_lock_monitor shows the followings.
------------
TRANSACTIONS
------------
Trx id counter 0 5905
Purge done for trx's n:o < 0 5902 undo n:o < 0 0
Total number of lock structs in row lock hash table 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 19607, OS thread id 45068
MySQL thread id 3, query id 37 127.0.0.1 root
---TRANSACTION 0 5903, ACTIVE 29 sec, process no 19597, OS thread id 36874 inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 1, query id 36 127.0.0.1 root update
insert into table2 values(102)
------- TRX HAS BEEN WAITING 29 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 210 n bits 80 index `col1` of table `oss1/table2
` trx id 0 5903 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits
0
 0: len 4; hex 80000067; asc    g;; 1: len 6; hex 00000007d108; asc       ;;

------------------
TABLE LOCK table `oss1/table2` trx id 0 5903 lock mode IX
RECORD LOCKS space id 0 page no 210 n bits 80 index `col1` of table `oss1/table2
` trx id 0 5903 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits
0
 0: len 4; hex 80000067; asc    g;; 1: len 6; hex 00000007d108; asc       ;;

---TRANSACTION 0 5902, ACTIVE 37 sec, process no 19598, OS thread id 40971
4 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 2, query id 34 127.0.0.1 root
TABLE LOCK table `oss1/table2` trx id 0 5902 lock mode IX
RECORD LOCKS space id 0 page no 210 n bits 80 index `col1` of table `oss1/table2
` trx id 0 5902 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits
32
 0: len 4; hex 80000066; asc    f;; 1: len 6; hex 00000007d107; asc       ;;

RECORD LOCKS space id 0 page no 192 n bits 80 index `GEN_CLUST_INDEX` of table `
oss1/table2` trx id 0 5902 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits
0
 0: len 6; hex 00000007d107; asc       ;; 1: len 6; hex 00000000170e; asc
;; 2: len 7; hex 00000000ce0793; asc        ;; 3: len 4; hex 8000006e; asc    n;
;

RECORD LOCKS space id 0 page no 210 n bits 80 index `col1` of table `oss1/table2
` trx id 0 5902 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits
0
 0: len 4; hex 80000067; asc    g;; 1: len 6; hex 00000007d108; asc       ;;
---

"lock_mode X locks gap before rec" means that it uses next-key locking, doesn't it?

Thank you for your quick reply.

I understand that insert uses next-key locking.
But, from innodb_lock_monitor result, update using index seems to use
next-key locking.

--SQL statement
start transaction;
update table2 set col1=110 where col1=102;

--The result of innodb_lock_monitor

---TRANSACTION 0 5891, ACTIVE 28 sec, process no 19598, OS thread id 40971
4 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 2, query id 13 192.168.0.1 root
TABLE LOCK table `oss1/table2` trx id 0 5891 lock mode IX
RECORD LOCKS space id 0 page no 210 n bits 80 index `col1` of table `oss1/table2
` trx id 0 5891 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits
32
 0: len 4; hex 80000066; asc    f;; 1: len 6; hex 00000007d107; asc       ;;

RECORD LOCKS space id 0 page no 192 n bits 80 index `GEN_CLUST_INDEX` of table `
oss1/table2` trx id 0 5891 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits
0
 0: len 6; hex 00000007d107; asc       ;; 1: len 6; hex 000000001703; asc
;; 2: len 7; hex 00000000ce0737; asc       7;; 3: len 4; hex 8000006e; asc    n;
;

RECORD LOCKS space id 0 page no 210 n bits 80 index `col1` of table `oss1/table2
` trx id 0 5891 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits
0
 0: len 4; hex 80000067; asc    g;; 1: len 6; hex 00000007d108; asc       ;;

The last record says "lock_mode X locks gap before rec".
It means that update(using index) uses next-key locking , isn't it?

Thank you for your example. I could repeat this problem using 4.1.8. I'm
currently working on patch to fix this problem.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html