MySQL Bugs: #39657: InnDB - 2 simple update queries cause deadlock.

Bug #39657	InnDB - 2 simple update queries cause deadlock.
Submitted:	26 Sep 2008 2:46	Modified:	13 May 2010 16:03
Reporter:	SeWoong Jeon	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0.40 Enterprise	OS:	Linux (Cent OS 4.5)
Assigned to:		CPU Architecture:	Any
Tags:	deadlock, innodb

Description:
I have some problem about Innodb deadlock.

(Trx1 - auto commit) UPDATE tb_sample SET cnt=cnt+1 WHERE pk=10 
(Trx2 - auto commit) UPDATE tb_sample SET title='test' WHERE pk=10

Trx1 get S-Lock for read "cnt"
Trx2 request X-Lock, but wait for end of Trx1
Trx1 request X-Lock for update, but wait for end of Trx2
--> DeadLock!! Trx2 was roll-backed by Innodb

MSSQL use U-Lock to prevent such situration of deadlock in repeatable read or serializable transaction.
http://msdn.microsoft.com/en-us/library/ms175519.aspx

If InnoDB doesn't have U-Lock, I think Trx1 have to get X-Lock first, not S-Lock.

===================================================
this is my real deadlock message
Primary of "buddylist" table is "userid + buddyid"
===================================================

*** (1) TRANSACTION:
TRANSACTION 0 1340575717, ACTIVE 0 sec, process no 12769, OS thread id 1262823776 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368
MySQL thread id 648655934, query id 7991043214 192.168.1.80 was updating
DELETE FROM MyDB.buddylist WHERE userid='aaa@domain.com' AND buddyid='bbb@domain.com' AND (btype='1' OR btype='2' OR btype='3')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 669 page no 226142 n bits 248 index `PRIMARY` of table `MyDB/buddylist` trx id 0 1340575717 lock_mode X locks rec but not gap waiting
Record lock, heap no 163 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 19; hex 726a73646c32303430406e617665722e636f6d; asc aaa@domain.com;; 1: len 18; hex 676f766c34323037406e617665722e636f6d; asc bbb@domain.com;; 2: len 6; hex 00004aee94ad; asc J ;; 3: len 7; hex 000000002d24b6; asc -$ ;; 4: len 1; hex 81; asc ;; 5: len 8; hex 80001243736601be; asc Csf ;;

*** (2) TRANSACTION:
TRANSACTION 0 1340575719, ACTIVE 0 sec, process no 12769, OS thread id 1297701216 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1216
MySQL thread id 648655935, query id 7991043224 192.168.1.79 was Updating
UPDATE MyDB.buddylist SET btype=btype|4 WHERE userid='aaa@domain.com' AND buddyid='bbb@domain.com'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 669 page no 226142 n bits 248 index `PRIMARY` of table `MyDB/buddylist` trx id 0 1340575719 lock mode S locks rec but not gap
Record lock, heap no 163 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 19; hex 726a73646c32303430406e617665722e636f6d; asc aaa@domain.com;; 1: len 18; hex 676f766c34323037406e617665722e636f6d; asc bbb@domain.com;; 2: len 6; hex 00004aee94ad; asc J ;; 3: len 7; hex 000000002d24b6; asc -$ ;; 4: len 1; hex 81; asc ;; 5: len 8; hex 80001243736601be; asc Csf ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 669 page no 226142 n bits 248 index `PRIMARY` of table `MyDB/buddylist` trx id 0 1340575719 lock_mode X locks rec but not gap waiting
Record lock, heap no 163 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 19; hex 726a73646c32303430406e617665722e636f6d; asc aaa@domain.com;; 1: len 18; hex 676f766c34323037406e617665722e636f6d; asc bbb@domain.com;; 2: len 6; hex 00004aee94ad; asc J ;; 3: len 7; hex 000000002d24b6; asc -$ ;; 4: len 1; hex 81; asc ;; 5: len 8; hex 80001243736601be; asc Csf ;;

*** WE ROLL BACK TRANSACTION (2)

How to repeat:
Can't Repeat.

This is a known InnoDB problem that should be fixed eventually. Please, check if this is still repeatable with 5.1.28.

Correcting the status. Valery, when you say 'a known problem', does it have a bug number? And in your opinion, is it really a bug, or just something that the customer doesn't like about InnoDB because it doesn't behave like MSSQL. The latter case would make it a feature request. How a deadlock is a bug and not the fact of life about database systems?

Hi, Michael.
Do you think I hate InnoDB because it doesn't behave like MSSQL?
Please let there is no misunderstanding about this.
I love MySQL and InnoDB. :-)

Simple update query like "UPDATE TABLE SET col=col+1 WHERE PK=$val", should get X-lock first, not S-lock.
Because InnoDB does not have U-Lock (like MSSQL).
It is point of this problem.

If this deadlock situration is normal, how can I avoid this deadlock situration?
"BEGIN; SELECT @val:=col .. FOR UPDATE; UPDATE .. SET col=@val+1 ..; COMMIT" ??

>Hi, Michael.

thank you for your prompt response. 
>Do you think I hate InnoDB because it doesn't behave like MSSQL?

no, not at all

>Please let there is no misunderstanding about this.
>I love MySQL and InnoDB. :-)

it's just that today I didn't really have time to concentrate properly or even to formulate my question clearly. The question is (was), does the situation conform to the transaction isolation level? If so, the application has to try again. If no, it's a bug in InnoDB no matter how we love InnoDB. Actually I was pretty sure the answer was 'no, it's a bug in InnoDB', I was just confused by the mention of the U-lock which is something I don't know what it is and didn't have the time today to Google it. 

>Simple update query like "UPDATE TABLE SET col=col+1 WHERE PK=$val", should get X-lock
>first, not S-lock.

I do agree. 

>Because InnoDB does not have U-Lock (like MSSQL).
>It is point of this problem.

can you give me the table structure? I can guess that PK must be the primary key and col is not. What interests me most, is any of col or PK, or any other column an autoinc? 

>If this deadlock situration is normal,

no this can't be normal

> how can I avoid this deadlock >situration?
"BEGIN; SELECT @val:=col .. FOR UPDATE; UPDATE .. SET col=@val+1 ..; COMMIT" ??

well I think the ref manual chapter on the InnoDB engine says that 
SELECT @val:=col .. FOR UPDATE

would acquire an X-lock on every row it would find, so we do seem to have a problem here. A complete CREATE TABLE statement, including each index on the table, would be a good starting point for the investigation.

Also, 'a known problem', how known? A bug number or just a reference to a blog entry would be nice. And, how reproducible is it? 
Sorry for asking trivial questions but I'm a bit new to MySQL/Innodb, I used to work with pretty much everything else that's out there except Sybase or mssql which are (used to be when mssql started) the same thing anyway

Michael, thank you for response. 

We use REPEATABLE-READ isolation level.
And.. Actually, we use dual-master (master-master) replication.
Roll-backed update query was executed by SQL Thread, and SQL Thread was stopped.

Table structure is very very simple.
and, you can find 2 queries in my original message. (innodb status)

CREATE TABLE `buddylist` (
  `userid` varchar(36) NOT NULL default '',
  `buddyid` varchar(36) NOT NULL default '',
  `btype` tinyint(4) default NULL,
  `crdate` datetime default NULL,
  PRIMARY KEY  (`userid`,`buddyid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I also want to know whether this is known problem.
Valeriy, Can you give us more information about this problem ?

I meant this old feature request, bug #21356.

Got it, finally, so the replication has nothing to do with it, but rather it's
a duplicate of a well known problem, http://bugs.mysql.com/bug.php?id=21356.
Thanks for the reference, Valeriy.
My opinion on lock escalation taking priority over existing X-waiter maybe not as strong as Valeriy's but my opinion doesn't matter too much. I do suspect that there may be applications out there that would start perform worse after this change. Or more generally, for most code changes there exists an application that will suffer. 
Heikki already declared http://bugs.mysql.com/bug.php?id=21356 a feature request, so I'm setting the status back to 'Verified', assigning to Sunny, setting S5. I'd also suggest triaging it as D5, and in fact I'd simply set the status of this one to be 'Duplicate', but I'll leave it to Sunny to decide.
Also, if I understand correctly MySQL procedures, a feature request, when/if implemented is unlikely to be backported to an older release. 
Regardless of the potential usefulness of U locks in InnoDB I can't see how lack of row level intent locks can be a bug. It's definitely a feature request.