Bug #95230 SELECT ... FOR UPDATE on a gap in repeatable read should be exclusive lock
Submitted: 2 May 21:06 Modified: 14 May 12:33
Reporter: Domas Mituzas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Locking Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[2 May 21:06] Domas Mituzas
Description:
when acquiring locks, 'FOR UPDATE' means exclusive lock.
alas, when doing 'SELECT ... FOR UPDATE' on a gap, the returned lock is shared - and therefore other transaction can acquire same lock and think it can operate on the data it locked (either same table, or use that as an advisory lock).

this breaks the locking contract - two transactions should not be able to succeed acquiring locks that they thought are exclusive and end up with shared locks. 

it is also painful to resolve these situations in applications.

How to repeat:
create table ta (a int primary key); 

T1:
begin
select * from ta where a=5 for update;
insert into ta (a) values (5);

T2:
begin
select * from ta where a=6 for update;
insert into ta (a) values (6);

Suggested fix:
exclusive locks should be exclusive
[2 May 21:06] Domas Mituzas
(I could always do LOCK IN SHARE MODE if I wanted a shared lock!)
[7 May 14:27] Sinisa Milivojevic
Hi Domas,

Thank you very much for your bug report.

This is the output I get from the innodb status:

-----------------------------------------------------------------------

------------
TRANSACTIONS
------------
Trx id counter 26136
Purge done for trx's n:o < 26134 undo n:o < 0 state: running but idle
History list length 6
Total number of lock structs in row lock hash table 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 26135, ACTIVE 78 sec
2 lock struct(s), heap size 1200, 1 row lock(s)
MySQL thread id 8, OS thread handle 123145590644736, query id 18 localhost sinisa starting
SHOW ENGINE INNODB STATUS
TABLE LOCK table `test`.`ta` trx id 26135 lock mode IX
RECORD LOCKS space id 54 page no 4 n bits 80 index PRIMARY of table `test`.`ta` trx id 26135 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 6; hex 00000000660d; asc     f ;;
 2: len 7; hex 82000000eb0144; asc       D;;

---TRANSACTION 26134, ACTIVE 166 sec
2 lock struct(s), heap size 1200, 1 row lock(s)
MySQL thread id 7, OS thread handle 123145590341632, query id 11 localhost sinisa
TABLE LOCK table `test`.`ta` trx id 26134 lock mode IX
RECORD LOCKS space id 54 page no 4 n bits 80 index PRIMARY of table `test`.`ta` trx id 26134 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000660c; asc     f ;;
 2: len 7; hex 81000000ce0110; asc        ;;

----------------------------------------------------------------------------

The only difference is that I have inserted rows before the start of any of those two transactions.

Let me know why should there be locks on the rows that do not exist ???

Also, please let me know why do you think that rows inserted in one open and uncommitted transaction should be visible in the other transaction.

It could be quite possible that this is my mistake, so please, provide me with the additional feedback so that I can repeat the behaviour that you see ........

Thank you very much, in advance.
[13 May 17:57] Domas Mituzas
Sinisa,

why should be locks on rows that do not exist?  because right now these locks already exist, just in share mode, and they are making transactions prone to deadlocks. these locks are being used to make repeatable read suitable for statement based binary logging.

also, if you paid more attention, you'd see that transactions are dealing with mutually exclusive sets of rows, it is just the gap lock mechanics that unify them.

my point here is that current locking method - shared locks on gaps - is prone to deadlocks as it is impossible to structure a transaction that would not have deadlocks if it cannot acquire correct lock to begin with. 

I understand that if the rows exist, then gaps are smaller and locking does not end up being that problematic. 

One of the problems with deadlocks in MySQL is that deadlock detection is too expensive and breaks InnoDB kernel entirely at larger concurrencies, therefore one needs to try to build applications in a way that avoids deadlocks. shared-only gap locks are breaking that ability.
[14 May 12:33] Sinisa Milivojevic
Hi Domas,

Thank you for the additional information. Your last comment made it quite clear what you were after.

I think that you are right and this would make a fine feature request. I will also write about it internally.

Thank you for your contribution.

Verified as a feature request.