mysql 5.6 read-committed隔离级别下并发插入唯一索引导致死锁一例(GAP锁的问题)
今天,某个环境又发生了死锁,如下:
*** (1) TRANSACTION:
TRANSACTION 735307073, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 2754, OS thread handle 0x7f29cd89a700, query id 751744317 127.0.0.1 osm update
insert into tb_trd_secu_command(
machine_date, company_no, product_id,
product_code, product_name, unit_id, unit_code,
unit_name, asset_account, stock_account, command_operator,
command_executor, command_date, command_time, command_id,
batch_no, exchange_no, asset_type, stock_type,
stock_code, stock_name, currency_type, command_dir,
limit_price, command_qty, command_amt, order_qty,
cancel_qty, command_status, execution_status, execution_amt,
execution_qty, frozen_amt)
value(
v_machine_date, v_company_no, v_product_id,
v_product_code, v_product_name, v_unit_id, v_unit_code,
v_unit_name, v_asset_account, v_stock_account, v_command_operator,
v_command_executor, v_command_date, v_command_time, v_command_id,
v_batch_no, v_exchange_no, v_asset_type, v_stock_type,
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10538 page no 4 n bits 296 index `idx_tb_trd_secu_command_1` of table `db_trd`.`tb_trd_secu_command` trx id 735307073 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 735307149, ACTIVE 0 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 2744, OS thread handle 0x7f29cd8db700, query id 751750715 127.0.0.1 osm update
insert into tb_trd_secu_command(
machine_date, company_no, product_id,
product_code, product_name, unit_id, unit_code,
unit_name, asset_account, stock_account, command_operator,
command_executor, command_date, command_time, command_id,
batch_no, exchange_no, asset_type, stock_type,
stock_code, stock_name, currency_type, command_dir,
limit_price, command_qty, command_amt, order_qty,
cancel_qty, command_status, execution_status, execution_amt,
execution_qty, frozen_amt)
value(
v_machine_date, v_company_no, v_product_id,
v_product_code, v_product_name, v_unit_id, v_unit_code,
v_unit_name, v_asset_account, v_stock_account, v_command_operator,
v_command_executor, v_command_date, v_command_time, v_command_id,
v_batch_no, v_exchange_no, v_asset_type, v_stock_type,
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10538 page no 4 n bits 296 index `idx_tb_trd_secu_command_1` of table `db_trd`.`tb_trd_secu_command` trx id 735307149 lock mode S locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10538 page no 4 n bits 296 index `idx_tb_trd_secu_command_1` of table `db_trd`.`tb_trd_secu_command` trx id 735307149 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)
其中idx_tb_trd_secu_command_1是唯一索引。
看了下参数,当前隔离级别是read-committed(动态参数),不过参数innodb_locks_unsafe_for_binlog(静态全局参数)是默认值OFF。
又仔细看了下官方文档, innodb_locks_unsafe_for_binlog和read-committed一方面是一样的,启用后,可以使得
InnoDB
gap锁最小化,但是在两种场景(外键约束和唯一索引)中,仍然不可避免的存在gap锁。
不管事务管理级别如何设置,只要存在唯一性约束并且可能insert/delete/update同一key值的记录,死锁都将无法100%的避免,只能是概率降低。
mysql show engine innodb status解析
https://www.percona.com/blog/2014/10/28/how-to-deal-with-mysql-deadlocks/
相关文章
- mysql备份与还原
- Windows环境配置Apache+Mysql+PHP
- 【MySQL从入门到精通】【高级篇】(二十六)建了索引就能用么?我看未必。来看看几种索引失效的情况吧
- 阿里百度华为如何使用MySQL给字符串加索引
- 一文搞懂MySQL事务和索引所有知识点
- mysql索引之哈希索引
- MySQL 普通索引、唯一索引和主索引
- 面试官问我MySQL索引,我
- 【MySQL】锁机制、事务、并发控制、引擎、索引 总结
- MySQL 中如何计算一个索引的长度
- 面试官:mysql索引会缓存内存吗?
- mysql大事务优化
- MySql在服务器上使用问题的总结
- Entity Framework with MySQL 学习笔记一(乐观并发)
- MySQL数据库存储varchar时多大长度会出现行迁移?
- MySQL:通过增加索引进行SQL查询优化
- 如何允许外网可以连接mysql数据库
- 【数据库技术】MySQL索引背后的数据结构及算法原理
- python3.4连接mysql
- 导致MySQL索引失效的几种常见写法
- mysql 创建唯一约束表
- MySQL高级-索引优化
- mysql升级-rpm安装
- 练习:Django与MySQL交互
- 我说MySQL联合索引遵循最左前缀匹配原则,面试官让我回去等通知
- MySQL 第六篇:索引与子查询
- MySQL用B+树(而不是B树)做索引的原因
- 『MySQL』索引类型 normal, unique, full text
- mysql:索引
- Mysql 时间格式化 DATE_FORMAT使用
- mysql 漏洞利用与提权