MySQL死锁与生成UID的存储过程 [英] MySQL deadlocks with stored procedure generating UID
问题描述
我有一个存储过程从票证"表生成UID,但是在负载下我会遇到很多死锁.每当我的任务需要新的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
我确实考虑过使用:
BEGIN
REPLACE INTO uid_data (stub) VALUES ('a');
SELECT LAST_INSERT_ID();
END
但是,我不确定并发连接是否安全,因为没有锁定,这与第一个使用SELECT FOR UPDATE
的过程不同.
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
.
这是桌子:
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 |
+---------------+-----------------+
这就是我从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.
推荐答案
执行以下操作:
CREATE TABLE tickets
(
uid serial
)
然后获取下一个uid:
Then to get the next uid:
BEGIN
INSERT INTO tickets VALUES (NULL);
SELECT LAST_INSERT_ID();
END
uid序列等效于
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.
这篇关于MySQL死锁与生成UID的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!