Today, a deadlock has occurred in an environment, as follows:
(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)
Where Idx_tb_trd_secu_command_1 is the only index.
The next parameter, the current isolation level is read-committed, but the parameter innodb_locks_unsafe_for_binlog is the default value off.
And looked carefully at the official documents,innodb_locks_unsafe_for_binlog和read-committed一方面是一样的,启用后,可以使得InnoDB
gap锁最小化,但是在两种场景(外键约束和唯一索引)中,仍然不可避免的存在gap锁。
Regardless of how the transaction management level is set, deadlocks cannot be avoided by 100% as long as there are unique constraints and may insert/delete/update records of the same key value, but only with a reduced probability.
MySQL 5.6 read-committed Isolation level concurrent insertion of a single cable-guided fatal lock case