具有自动生成的主键和唯一键的表上的 Mysql 死锁

Question

我有一个带有自动生成的主键和唯一键的表 sdr。我随机得到这些死锁错误，如下所示。它是一个基于 hibernate 的 Web 应用程序，可以触发这些插入查询。阅读 https://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html 上的 mysql 文档后，似乎可以使用 READ_COMMITTED 解决。顺便说一下，数据库已经设置为 tx_isolation = READ-COMMITTED。

我无法删除唯一键，因为业务逻辑需要检查记录是否已存在。此外，UI 为读取操作提供了唯一的键值。

由于下游应用程序的影响，我也无法删除自动生成的主列。

这个问题可以用规定的约束来解决吗？

更多详情:Mysql 5.6 InnoDB

TABLE 语句:

     CREATE TABLE `sdr` (   
        `s_d_r_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   
        `fk_d_r_id` bigint(20) unsigned NOT NULL,   
        `fk_s_s_id` int(10) unsigned NOT NULL,   
        `t_l_x_no` int(10) unsigned NOT NULL,   
        `t_l_y_no` int(10) unsigned NOT NULL,   
        `h_no` int(11) DEFAULT NULL,   
        `w_no` int(11) DEFAULT NULL,   
        `o_tx` varchar(4000) DEFAULT NULL,   
        `f_d_c_tx` varchar(4000) DEFAULT NULL,   
        `l_c_no` int(10) unsigned NOT NULL DEFAULT '0',  
         `d_in` tinyint(1) NOT NULL DEFAULT '0',   
        `c_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  
         `c_u_id` bigint(20) unsigned NOT NULL,   
        `l_m_ts` timestamp NOT NULL DEFAULT '1970-01-01 05:00:00',  
         `l_m_user_id` bigint(20) unsigned NOT NULL,  
         `r_no` decimal(3,0) NOT NULL DEFAULT '0',  
         PRIMARY KEY (`s_d_r_id`),   
        UNIQUE KEY `uk_s_d_r` (`fk_d_r_id`,`fk_s_s_id`,`t_l_x_no`,`t_l_y_no`,`r_no`),   
    KEY `i_s_fk_s_s_id` (`fk_s_s_id`),   
        CONSTRAINT `r_d_s` FOREIGN KEY (`fk_d_r_id`) 
        REFERENCES `d_r` (`D_R_ID`)  ON DELETE NO ACTION ON UPDATE NO ACTION, 
  CONSTRAINT `r_s_s` FOREIGN KEY (`fk_s_s_id`) 
    REFERENCES `s_s` (`s_s_id`) 
         ON DELETE NO ACTION ON UPDATE NO ACTION ) 
        ENGINE=InnoDB AUTO_INCREMENT=45904 DEFAULT CHARSET=utf8;

死锁日志:

    2015-10-11 09:25:18 7f30cb700700InnoDB: transactions deadlock detected,
 dumping detailed information.
        2015-10-11 09:25:18 7f30cb700700
        *** (1) TRANSACTION:
        TRANSACTION 276584110286, ACTIVE 1 sec inserting
        mysql tables in use 1, locked 1
        LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), 
    undo log entries 1
        MySQL thread id 31640, OS thread handle 0x7f3510eff700, query id 
    1796766867 server-1 21.222.12.123 db_user update
        insert into S_D_R (
    D_IN, L_C_NO, C_TS, C_U_ID, L_M_TS, L_M_U_ID, 
    F_D_C_TX, FK_D_R_ID, H_NO, FK_S_S_ID, O_TX, 
    T_L_X_NO, T_L_Y_NO, W_NO) values 
    (0, 0, '2015-10-11 13:25:18.036', 123, '2015-10-11 13:25:18.036', 
    123, '[]', 1234567, -1, 3, 'some', 555, 678, -1)
        *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
        RECORD LOCKS space id 4577 page no 9296 n bits 528 index 
    `uk_s_d_r` of table `test`.`s_d_r` trx id 276584110286 
    lock mode S waiting
        *** (2) TRANSACTION:
        TRANSACTION 276584110033, ACTIVE 1 sec inserting
        mysql tables in use 1, locked 1
        7 lock struct(s), heap size 1184, 9 row lock(s),
     undo log entries 7
        MySQL thread id 32293, OS thread handle 0x7f30cb700700, 
    query id 1796767643 server-2 21.222.12.124 db_user update
        insert into S_D_R (
    D_IN, L_C_NO, C_TS, C_U_ID, L_M_TS, L_M_U_ID, 
    F_D_C_TX, FK_D_R_ID, H_NO, FK_S_S_ID, O_TX, T_L_X_NO, 
    T_L_Y_NO, W_NO) values (0, 0, '2015-10-11 13:25:18.081', 123, 
    '2015-10-11 13:25:18.081', 123, '[]', 6789064, -1, 7, 
    'someother', 550, 15, -1)
        *** (2) HOLDS THE LOCK(S):
        RECORD LOCKS space id 4577 page no 9296 n bits 528 index 
    `uk_s_d_r` of table `test`.`s_d_r` trx id 276584110033 
    lock_mode X locks rec but not gap
        *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
        RECORD LOCKS space id 4577 page no 9296 n bits 536 index 
    `uk_s_d_r` of table `test`.`s_d_r` trx id 276584110033 
    lock_mode X locks gap before rec insert intention waiting
        *** WE ROLL BACK TRANSACTION (1)

Answer 1

插入/更新语句的更多代码将有助于确定并发是否正确完成并在完成后释放。当下一个调用进入时，似乎有东西保留在数据库中。这些是在事务中完成的还是在单个语句中完成的？