程序并发update同一张表或者并发insert 一张表

使用索引解决MySQL死锁问题
摘要
MYSQL死锁解决过程
开启mysql死锁监控
查死锁信息
问题描述
问题解决方案
结论
摘要
当程序并发update同一张表，或者并发insert 一张表的时候都会出现数据库死锁问题。当并发insert一张表可能会出现间隙锁（小概率事件），这里先不谈，我主要描述update的常见的一种场景。

MYSQL死锁解决过程
开启mysql死锁监控
create table InnoDB_monitor(a INT) engine=InnoDB;
linux 环境重启myql

查死锁信息
查看死锁日志
在linux系统中：
先用 root 账号登录 mysql -uroot -proot。
然后用命令查看死锁信息 show engine innodb status \G;

在日志中找 LATEST DETECTED DEADLOCK 字样的日志，就是死锁日志。下面是我整理并截取的部分日志

问题描述
可以看到出现死锁的两条SQL分别是：
UPDATE BAL_REL SET USE_TOTAL=IFNULL(TOTAL_NUM,0)+‘4293’ WHERE BALANCE_ID=3420655500398712 AND USER_ID = 3072141520643085 AND SERV_ID=13510100 and USE_TOTAL < IFNULL(TOTAL_NUM,0)+‘4293’
和
UPDATE BAL_REL SET USE_TOTAL=IFNULL(TOTAL_NUM,0)+‘33442’ WHERE BALANCE_ID=3420655500398712 AND USER_ID = 3072141540643857 AND SERV_ID=13510100 and USE_TOTAL < IFNULL(TOTAL_NUM,0)+‘33442’

看下表模型设计：
由于表数据量比较大，加了三个索引，而且都是BTREE索引。

这样对更新同一个BALANCE_ID的update SQL 就有问题。由于BTREE存在，update 会锁住 BALANCE_ID索引节点下 每一条记录，就不是行级锁了。
https://blog.csdn.net/sturgsslecofwe/article/details/97938152
https://blog.csdn.net/sturgsslecofwe/article/details/97938462
https://blog.csdn.net/sturgsslecofwe/article/details/97938714

问题解决方案
（1）优化表结构
添加自增长的主键：
通过主键ID ,update每条数据，这时候使用的都是行锁。
设置手动提交事务 分别执行SQL，set autocommit=0; 并发执行两个SQL没有出现死锁。
https://www.qichamao.com/person/caa97deee42266f22643608681a394aa0db980a883c82b12ccacdab6ab20c3ca-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/243be674fb02d0815d1391628248bfe33c61e36924c0b04073b510a670199618-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/e49c2c4420212881731cfb1cb221ce6f9b241cd8ebb6226acb44e534f61974d9-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/c93e3196d74bf7b88dc39e2f4a500d6386de255cca113aa890774b3f49f795a2-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/2a0c42ff75ea7ad7d5a3610b13eaba4abd7c5e55a5e866407aa35ef8b6a6a68a-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/288a3c61f60aed3ba474a68131018bafa3375252ab9fd5de7c5e67276d493279-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/ca3985ef5bdf291d3a3e1132258476bca9de6573e6f1e1730e3d33445d44a418-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/a218d09b302ec463c75e54e9b74bbcc38882ebffe8a5913b348300e2c1d2f260a396766062fd556e8588ffae4bab3330-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/02c4b0a35379d1c6b0fb0cd3c3afcbba80cced413ff16851100f9e5af36aa491-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/e27c625cc4a90ef810e79c65ff29b38ded78f3076bc62f3f0e3bd5de131be77f-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/a6e580f6a12a79148df8043a94715be41e534714371606be88541f485ebe3e02-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/a17e4e3f36f9d5a9f1c82fb25c4b979367eaa0a25f4f3ac8466b5005f4ba01034ffaad6c1bd9bb7662489aa651d9011e-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/cea3baf7d3b697e4a42538440b7bff6b2614d62674c627bd0c1680d8dde12c8a-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/803b616c50170d67bc663130b4d66dd631767c0071f7239c05366d617e71145e-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/5fe0aa88e070fa282004b1d35925abf90648f68d0eb6169f8b22217a0c13f8075bfca1f02bc18e52d82bdfc7fafa480b-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/07786ecb72d31ba86ac979088355728e79791f8ec56034a665a5190243d5344f-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/d5be86a45e623dc038e44b761ead0864940739d063ce513bde4eb6e2c7afd3f9-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/0310d770565ee016b9afe452d1bbc48d198de31a68b5c80eb2e367328158fe791db1641776cf5e7b7e9a3206e55cd64b-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/ef1cc1cceef4fcd577c8b2784bc574f19824a1a12340deb25d41babd579cab40-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/ad441eaf72ba2048bfa4313f52e164a904045d0c66cba2dcf2e6f474883bf3c5-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/57b6120a214557e740d110ebb0414c2915487bdad30a90c63745cc3bfc34a7cc-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/42adc2a7d81792e60d6a7c80eed2a38e4e38af27def412d01b82902099eeae93-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/2ae8308db36b70ef7d1b387328da80d6a100f3ed805fc9295f73c8a10aa72910245e38d667688892add8d9e97f019796-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/4dbdf59f46928380f33cacb9622eb28445b1fe467e858626b3cec0922c710bd536bc11ba9b5b75d5dde9d665b177acad-2a6e52ffdd387b1510b9e27362ee9011
https://www.qichamao.com/person/ca843016999b4c1b133ba9fb46368bacbd7081bd6e38244cea1f49f7a313457f-2a6e52ffdd387b1510b9e27362ee9011

但是这样效率比较低，需要先查询到主键，然后update

（2）优化索引
可以看到SQL中where条件用了三个字段：BALANCE_ID，USER_ID，POOL_ID
添加一个组合索引 BALANCE_ID，USER_ID，POOL_ID

执行下SQL，没有出现锁等待，说明这种方式也是可以行,也不需要查询主键ID，相对来说效率会高点，但是会增加索引磁盘空间，insert的效率也会降低。

结论
我选择第二方式。但是设计表的时候，最好都加一个自增长的主键，存储结构会好一些，索引的效率也会好一些。

作者：一片-绿叶
来源：CSDN
原文：https://blog.csdn.net/weixin_41715077/article/details/82982530
版权声明：本文为博主原创文章，转载请附上博文链接！