• 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%的避免,只能是概率降低。

  • 相关阅读:
    Activator.CreateInstance 反射实例化对象
    MVC Form提交
    Redis 下载
    List<T> 序列化与反序列化
    快速反射DataTable
    数据库特性
    javascript判断文件大小
    MD5
    HttpHelper
    cacheHelper
  • 原文地址:https://www.cnblogs.com/zhjh256/p/6051839.html
Copyright © 2020-2023  润新知