首页 > 代码库 > 由SELECT ... FROM ... FOR UPDATE想到的
由SELECT ... FROM ... FOR UPDATE想到的
应用程序开发中有个较常见的场景, 查询某字段的值(该字段一般具有唯一性), 是否存在, 若不存在, 则插入一条记录, 反之, 就更新该记录.
常见的方法是, SELECT... FROM ... FOR UPDATE查询下, 根据SELECT返回情况, 进行相应的操作. 实践中发现, 并发量较大时, 可能会有较多死锁的情况发生,下面利用tb1表演示该问题.
tb1的表结构为:
mysql>SHOW CREATE TABLE tb1 \G
***************************1. row ***************************
Table: tb1
CreateTable: CREATE TABLE `tb1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`category` varchar(20) NOT NULL DEFAULT ‘‘,
`quantity` bigint(20) NOT NULL DEFAULT ‘0‘,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_category` (`category`)
)ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
1 rowin set (0.00 sec)
Session1:
mysql>BEGIN;
QueryOK, 0 rows affected (0.00 sec)
mysql>SELECT * FROM tb1 WHERE category = ‘ko_007‘ FOR UPDATE;
Emptyset (0.00 sec)
Session2:
mysql>BEGIN;
QueryOK, 0 rows affected (0.00 sec)
mysql>SELECT * FROM tb1 WHERE category = ‘ko_007‘ FOR UPDATE;
Emptyset (0.00 sec)
Session1插入category为‘ko_007‘的记录, 出现了锁等待.
mysql>INSERT INTO tb1 (category) VALUES (‘ko_007‘);
…
Session2也插入category为‘ko_007‘的记录, 死锁的情况发生了.
mysql>INSERT INTO tb1 (category) VALUES (‘ko_007‘);
ERROR1213 (40001): Deadlock found when trying to get lock; try restartingtransaction
针对死锁的产生, 从数据库本身来看, 是否还有其它方法完成上面的需求呢 …
MySQL提供了INSERT ... ON DUPLICATE KEY UPDATE语法, 其运行方式是: 若主键(或唯一键)不存在, 就插入一条记录; 若主键存在, 则更新该记录.
对于上述需求, 单个SQL语句即可搞定: 有则更新, 无则插入.
INSERTINTO tb1 (category) VALUES (‘ko_007‘) ON DUPLICATE KEY UPDATE quantity =quantity + 1;
近日, 项目的一个分布式日志产生量统计程序, 从Kafka订阅消息处理后, 将结果写入数据库, 就使用了INSERT ... ON DUPLICATE KEY UPDATE. 其虽不像SELECT ... FROM ... FOR UPDATE容易发生死锁, 却发现了另外一个问题: 与数据表主键相关的AUTO_INCREMENT值非常大, 主键值呈跳跃式增长, 这样持续下去, 主键值要不够用了啊!
最初想到的可能是, innodb_autoinc_lock_mode设置为2引起的, 确认后, 其值是1. 哎, 原因还未找到.
测试下INSERT... ON DUPLICATE KEY UPDATE, 过程如下面所示:
tb1表中存在category 为‘ko_007‘的记录:
mysql>SELECT * FROM tb1 WHERE category = ‘ko_007‘;
+----+----------+----------+
| id| category | quantity |
+----+----------+----------+
| 10| ko_007 | 1 |
+----+----------+----------+
1 rowin set (0.00 sec)
此时查看表结构, AUTO_INCREMENT为12:
mysql>SHOW CREATE TABLE tb1 \G
***************************1. row ***************************
Table: tb1
CreateTable: CREATE TABLE `tb1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`category` varchar(20) NOT NULL DEFAULT ‘‘,
`quantity` bigint(20) NOT NULL DEFAULT ‘0‘,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_category` (`category`)
)ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4
1 rowin set (0.00 sec)
执行下面的SQL:
mysql>INSERT INTO tb1 (category) VALUES (‘ko_007‘) ON DUPLICATE KEY UPDATE quantity =quantity + 1;
QueryOK, 2 rows affected (0.00 sec)
查看category 为‘ko_007‘的记录, 和表结构, 可看到quantity 字段更新为2, AUTO_INCREMENT增加了1, 变为13, 如下面所示:
mysql>SELECT * FROM tb1 WHERE category = ‘ko_007‘;
+----+----------+----------+
| id| category | quantity |
+----+----------+----------+
| 10| ko_007 | 2 |
+----+----------+----------+
1 rowin set (0.00 sec)
mysql>SHOW CREATE TABLE tb1 \G
***************************1. row ***************************
Table: tb1
CreateTable: CREATE TABLE `tb1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`category` varchar(20) NOT NULL DEFAULT ‘‘,
`quantity` bigint(20) NOT NULL DEFAULT ‘0‘,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_category` (`category`)
)ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4
1 rowin set (0.00 sec)
其实查看MySQL官方文档对于INSERT ... ON DUPLICATE KEY UPDATE的说明时，细心的话可发现文档早已说明了该语法UPDATE唯一记录时, AUTO_INCREMENT会增加的.
大致的原因是, MySQL在执行INSERT ... ON DUPLICATE KEY UPDATE时, 首先要AUTO_INCREMENT+1获取可用的自增值, 继续后面的逻辑时, MySQL发现这是个UPDATE操作, 继而更新相应的记录.
既然整数型的自增主键值有用完的风险, 那使用VARCHAR数据类型的字段作为主键, 就避开该问题呀, 确实是, 调整后的表结构如下:
mysql>SHOW CREATE TABLE tb2 \G
***************************1. row ***************************
Table: tb2
CreateTable: CREATE TABLE `tb2` (
`category` varchar(20) NOT NULL DEFAULT ‘‘,
`quantity` bigint(20) NOT NULL DEFAULT ‘0‘,
PRIMARY KEY (`category`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 rowin set (0.00 sec)
但这也可能带来某些副作用,如数据表存储空间变大, 主从复制效率降低等等…
本文出自 “感动自己” 博客，请务必保留此出处http://coveringindex.blog.51cto.com/12533373/1954051
由SELECT ... FROM ... FOR UPDATE想到的