MySQL deadlocks with stored procedure generating UID

Question

I have a stored procedure that generates a UID from the ticket table, but when I load it, I get a lot of dead ends. I call this procedure many times from several concurrent connections when my task needs a new UID.

BEGIN DECLARE a_uid BIGINT(20) UNSIGNED; START TRANSACTION; SELECT uid INTO a_uid FROM uid_data FOR UPDATE; # Lock INSERT INTO uid_data (stub) VALUES ('a') ON DUPLICATE KEY UPDATE uid=uid+1; SELECT a_uid+1 AS `uid`; COMMIT; END 

I really considered using:

 BEGIN REPLACE INTO uid_data (stub) VALUES ('a'); SELECT LAST_INSERT_ID(); END 

However, I was not sure that it would be safe with simultaneous connections, since there is no blocking, unlike the first procedure with SELECT FOR UPDATE .

Here is the table:

 mysql> DESCRIBE uid_data; +-------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+----------------+ | uid | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | stub | char(1) | NO | UNI | NULL | | +-------+---------------------+------+-----+---------+----------------+ 

I have configured transaction isolation in transactions:

 mysql> SHOW VARIABLES LIKE 'tx_isolation'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | READ-COMMITTED | +---------------+-----------------+ 

This is what I get from SHOW ENGINE INNODB STATUS;

 ... ... dozens and dozens of the following record locks... Record lock, heap no 1046 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 1; hex 61; asc a;; 1: len 8; hex 00000000000335f2; asc 5 ;; Record lock, heap no 1047 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 1; hex 61; asc a;; 1: len 8; hex 00000000000335f1; asc 5 ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 13 page no 4 n bits 1120 index `stub` of table `my_db`.`uid_data` trx id 13AA89 lock_mode X waiting Record lock, heap no 583 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 1; hex 61; asc a;; 1: len 8; hex 00000000000334a8; asc 4 ;; *** WE ROLL BACK TRANSACTION (1) 

I would be grateful if someone could explain what is happening and how they can be avoided.

Answer 1

Do it:

 CREATE TABLE tickets ( uid serial ) 

Then, to get the following uid:

 BEGIN INSERT INTO tickets VALUES (NULL); SELECT LAST_INSERT_ID(); END 

uid serial is equivalent

 uid BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment 

You should not experience any deadlocks with this approach and you can throw as many connections on it as you want.

Answer 2

In this scenario, a deadlock occurs:

Transaction 1: requests a lock ( SELECT...FOR UPDATE ) and receives it

Transaction 2: requests a lock ( SELECT...FOR UPDATE ) and must wait

Transaction 1: tries to insert, removes the duplicate, therefore it updates ( INSERT...ON DUPLICATE KEY UPDATE ) => deadlock

I'm not too sure about the reason, I suspect this has something to do with ON DUPLICATE KEY UPDATE . I'm still investigating and will be back if I find out. Strike>

[Edit] Blocking happens even with:

 BEGIN START TRANSACTION; SELECT uid FROM uid_data FOR UPDATE; UPDATE uid_data SET uid = uid +1; -- here, a deadlock would be detected in a blocked, concurrent connection COMMIT; END 

How about this:

 BEGIN START TRANSACTION; UPDATE uid_data SET uid = uid +1; SELECT uid FROM uid_data; COMMIT; END 

You can completely remove your stub colum. The only downside is that you have to initialize your uid_data with one line.

Answer 3

You can try using

 UPDATE uid_data SET uid = LAST_INSERT_ID(uid+1); SELECT LAST_INSERT_ID(); 

on a table for example

 CREATE TABLE `uid_data` ( `uid` BIGINT(20) UNSIGNED NOT NULL ) COLLATE='utf8_general_ci' ENGINE=MyISAM; 

It is thread safe and does not lock the table if it is MyISAM (except during the actual update statement).