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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With