1

I find a similar question asked here Deadlock using SELECT ... FOR UPDATE in MySQL. But the answer didn't really explain why this happens.

The situation is quite easy to reproduce @ Mysql 5.7.17 (or other versions in 5.5 or 5.6):

CREATE TABLE `test` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `search` (`val`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert into test set val='pre-lock';

==session1==

start transaction;

select * from test where val='pre-lock' for update;

==session2==

start transaction;

select * from test where val='pre-lock' for update;

==session1==

insert into test set val='/a/b/c'; //note that if the set val='pre-lock111', there will be no deadlock at session 2

==session2==

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

The result of show engine innodb status:

LATEST DETECTED DEADLOCK
------------------------
2017-04-06 23:54:03 0x7000057db000
*** (1) TRANSACTION:
TRANSACTION 1333, ACTIVE 18 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5, OS thread handle 123145394155520, query id 62 localhost root Sending data
select * from test where val='pre-lock' for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 4 n bits 72 index search of table `test_tnx`.`test` trx id 1333 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 7072652d6c6f636b; asc pre-lock;;
 1: len 8; hex 8000000000000001; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 1332, ACTIVE 29 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 62, OS thread handle 123145394434048, query id 63 localhost root update
insert into test set val='/a/b/c'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 24 page no 4 n bits 72 index search of table `test_tnx`.`test` trx id 1332 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 7072652d6c6f636b; asc pre-lock;;
 1: len 8; hex 8000000000000001; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 4 n bits 72 index search of table `test_tnx`.`test` trx id 1332 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 7072652d6c6f636b; asc pre-lock;;
 1: len 8; hex 8000000000000001; asc         ;;

*** WE ROLL BACK TRANSACTION (1)

My perception is that the insert clause in session 1 acquires a gap lock which is somehow overlapping with the gap lock by the select for update. And therefore, this create a deadlock. However, I couldn't find any document supporting my idea. Please help to explain.

Updated 1 on 7th. Apr. 2017:

My full use case is like follows. Each session starts a transaction. It first locks the "pre-lock" row for uniqueness, after which performs a set of insertions. Then, it commits all insertions and release the lock. To my understand, if I remove the "pre-lock", two transactions may result in deadlock, and both fail (mysql kills one and let the other go as pointed out by @Michael - sqlbot). Meanwhile, it is allowed for some other sessions to read the content of the table already been committed before， and therefore, exclusively locking the table is not the cue.

Updated 2 on 7th. Apr. 2017:

It it quite a long story to to fully explain my objective. In short, I want to use mysql as a locking service for an external system. These insertions I mentioned is a set of external locks. It can not be made unique since I have read/write locks, and want to support kind of wildcard. Before these insertions, I actually need to do a conflict analysis with a set of selections. Setting up a global pre-lock sounds a good solution for me to accomplish this task.

Community
  • 1
  • 1
Liqun Li
  • 31
  • 5
  • What's the use case in `SELECT ... FOR UPDATE` here? That's actually the best way to get deadlocks. – tadman Apr 07 '17 at 03:37
  • @tadman Thanks for your response. I tried to use "select for update" as a lock for uniqueness, i.e., to prevent two sessions from modifying the table simulteniously. I don't want to use lock table, since it is allowed for other sessions to read the table content. Maybe there are better ways to achieve this use case. – Liqun Li Apr 07 '17 at 03:56
  • I've got to ask why that's a problem. What are you trying to perform here that couldn't be handled with transactions? If you can better explain the task you're trying to perform it'd be easier to advise about the best approach. – tadman Apr 07 '17 at 03:57
  • @tadman Thanks. I've add more context in the question above. So far, I find workarounds like setting the isolation level to "read committe", or create a dedicated "pre-lock" table to avoid possible deadlock. – Liqun Li Apr 07 '17 at 04:06
  • That's still very much a description of your strategy, not your objective. If you're using that row as some kind of semaphore I think you've taking the wrong approach here. Normally you set up your schema with `UNIQUE` indexes to prevent duplicate data from being allowed, then use a `BEGIN TRANSACTION` to ensure that your operations complete entirely or not at all, you can just roll them back if you have trouble. Normally that minimizes the chance of deadlocks as if they're all doing the same order of operations, they won't lock resources out of order. – tadman Apr 07 '17 at 04:09
  • @tadman I agree with your suggestion. It it quite a long story to to fully explain my objective. In short, I want to use mysql as a locking service for an external system. These insertions I mentioned is a set of external locks. It can not be made unique since I have read/write locks, and want to support kind of wildcard. I didn't considered putting them following a certain order, which might be feasible. But, setting up a global pre-lock sounds simpler for me. I understand the drawbacks of using "select for update", and I really hope to make it clear why this deadlock happens. – Liqun Li Apr 07 '17 at 04:45
  • *two transactions may result in deadlock, and both fail.* No, that will not happen. Deadlock detection is designed to kill only enough transactions to remove the deadlock condition. Transactions deadlock *against other transactions*. Once a deadlock among 2 transactions is resolved by killing 1 of them, the other can proceed. – Michael - sqlbot Apr 07 '17 at 07:09
  • It's also not clear whether you understand that deadlocks are not *bad* -- they just *are*. That's why the error says **try restarting transaction**. You want these locks? Great -- start over, and ask again, and maybe you'll get them and be able to hold onto them next time, and you can proceed once he rolls back or commits. Since the competing transaction has now successfully escalated the lock you were blocking him on (from IX to X)... then, next time, you'll be waiting for the locks in a different order, by definition. Restart the transaction, and see what happens. – Michael - sqlbot Apr 07 '17 at 07:17
  • @Michael-sqlbot Thanks for pointing out the two transactions won't fail both. I realized that it is not really harmful to have deadlocks, and indeed I cound submit the transaction again. Actually, in my system, it is rare to see the deadlocks. So, deal with the deadlock is not a real issue. I'm just curious why this happens. – Liqun Li Apr 07 '17 at 07:26

0 Answers0