MySQL Bugs: #95230: SELECT ... FOR UPDATE on a gap in repeatable read should be exclusive lock

Bug #95230	SELECT ... FOR UPDATE on a gap in repeatable read should be exclusive lock
Submitted:	2 May 2019 21:06	Modified:	14 May 2019 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

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

(I could always do LOCK IN SHARE MODE if I wanted a shared lock!)

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.

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.

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.

How come this issue hasn't been tackled yet? IMO this is a serious flaw!

Hi,

This is a feature request and not a bug. This feature would be a major undertaking and it has yet to be decided whether to be implemented or not and also in which situations.

If a decision becomes positive, then it remains to be decided for which version would it be scheduled.

These decisions are made at a very high levels, to which Verification team does not have an access.