mysql 生成uid_MySQL死锁与存储过程生成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.

解决方案

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.