Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL deadlocks with stored procedure generating UID

I have a stored procedure generating UID's from a "ticket" table, but under load I'm getting lots of deadlocks. I'm calling this procedure many times from multiple concurrent connections whenever 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 did consider using:

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

However I wasn't sure if that would be safe with concurrent connections as there's no locking, unlike the first procedure with the SELECT FOR UPDATE.

Here's 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've setup for read-committed transaction isolation:

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

Here's what I'm getting back 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'd be grateful if someone could explain what's happening and how they can be avoided.

like image 325
Sencha Avatar asked Jul 05 '12 14:07

Sencha


1 Answers

Do this:

CREATE TABLE tickets
(
    uid serial
)

Then to get the next uid:

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

uid serial is equivalent to

uid BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment

You shouldn't experience any deadlocks with this approach and can throw as many connections at it as you like.

like image 193
Dave Hilditch Avatar answered Nov 23 '22 21:11

Dave Hilditch