8000 麻烦问下4和18的情况有办法避免吗？ · Issue #12 · aneasystone/mysql-deadlocks · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

麻烦问下4和18的情况有办法避免吗？ #12

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Open
804e opened this issue Nov 18, 2019 · 6 comments
Open

麻烦问下4和18的情况有办法避免吗？ #12

804e opened this issue Nov 18, 2 8000 019 · 6 comments
Labels
to-reproduce

Comments

@804e
Copy link
804e commented Nov 18, 2019 •
edited
Loading

你好，我遇到4和18的情况，事务隔离级别已经设置为read committed，但是无法完全避免此类情况，目前只能通过分布式锁保证更新不在同一条id上进行，有办法通过调整数据库操作避免此种情况吗？

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-11-17 20:35:45 0x2b9fc4081700
*** (1) TRANSACTION:
TRANSACTION 128001656, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 2903, OS thread handle 47972839069440, query id 13418085 192.168.0.11 test updating
DELETE FROM test_table WHERE id = 'test'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3800 page no 5 n bits 448 index PRIMARY of table `test_table` trx id 128001656 lock_mode X locks rec but not gap waiting
Record lock, heap no 319 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 7; hex 475a3033363135; asc test;;
 1: len 6; hex 000007a12677; asc     &w;;
 2: len 7; hex 33000002e42549; asc 3    %I;;
 3: len 1; hex 83; asc  ;;
 4: len 8; hex 8000000000000001; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 128001655, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2898, OS thread handle 47965188658944, query id 13418099 192.168.0.11 test update
INSERT INTO test_table (id, state) VALUES ('test', 3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3800 page no 5 n bits 448 index PRIMARY of table `test_table` trx id 128001655 lock_mode X locks rec but not gap
Record lock, heap no 319 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 7; hex 475a3033363135; asc test;;
 1: len 6; hex 000007a12677; asc     &w;;
 2: len 7; hex 33000002e42549; asc 3    %I;;
 3: len 1; hex 83; asc  ;;
 4: len 8; hex 8000000000000001; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3800 page no 5 n bits 448 index PRIMARY of table `test_table` trx id 128001655 lock mode S waiting
Record lock, heap no 319 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 7; hex 475a3033363135; asc test;;
 1: len 6; hex 000007a12677; asc     &w;;
 2: len 7; hex 33000002e42549; asc 3    %I;;
 3: len 1; hex 83; asc  ;;
 4: len 8; hex 8000000000000001; asc         ;;

*** WE ROLL BACK TRANSACTION (1)
@aneasystone
Copy link
Owner

一般来说有几种方法可以避免这个死锁：

  1. 代码中尽量不要出现 delete 某个 key 然后又插入相同 key 的场景，可以改成 INSERT ... ON DUPLICATE KEY UPDATE；
  2. 将这个 key 改成非唯一索引；
  3. 使用锁实现串行化处理，譬如你使用分布锁，或者在 DELETE 之前，先执行 SELECT * FROM test_table WHERE id = 'test' FOR UPDATE，使用悲观锁来避免死锁。

@804e
Copy link
Author
804e commented Nov 21, 2019

我的这个id是主键，这个没办法修改，之前用INSERT ... ON DUPLICATE KEY UPDATE已经发现被锁住一次了，目前只能通过分布式锁禁止用户同时发起登录行为。
不知道为什么，我本地用mysqlslap测试delete；insert行为根本无法重现触发死锁，只有在insert；delete的时候才出现，目前只能猜测登录的事务执行时间太长，导致了某些死锁行为，有没有其他方法可以重现？

@aneasystone
Copy link
Owner

不知道你的重现步骤是怎么样的？我之前有重现过案例18，你可以看下这个脚本，README 里写了我的重现步骤：https://github.com/aneasystone/mysql-deadlocks/blob/master/docker/db/t18.sql

@804e
Copy link
Author
804e commented Nov 21, 2019

我就是按照你的脚本测试的，是不是跟事务隔离级别有关？我的库是读提交

@jianhong-li
Copy link

我就是按照你的脚本测试的，是不是跟事务隔离级别有关？我的库是读提交

回答与当前 case 无关. 不过复现死锁真的与隔离级别有关.不同的隔离级别会产生不同的加锁情况.特别是 RR 与 RC 级别的区别.
RR 的间隙锁是大部分诡异的死锁产生的真凶.

@humanbeingxx
Copy link

case18我本地并不能复现。相反，如果把id换成unique key，就能稳定复现。
这是我的测试脚本。

    Connection connection1 = jdbcTemplate.getDataSource().getConnection();
    Connection connection2 = jdbcTemplate.getDataSource().getConnection();
    connection1.setAutoCommit(false);
    connection2.setAutoCommit(false);
    for (int i = 0; i < 1000; i++) {
        Thread thread1 = new Thread(() -> {
            try {
                connection1.createStatement().execute("delete from t18 where id = 4");
                connection1.createStatement().execute("insert into t18 values(4)");
                connection1.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        });
        Thread thread2 = new Thread(() -> {
            try {
                connection2.createStatement().execute("delete from t18 where id = 4");
                connection2.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        });
        thread1.start();
        thread2.start();
        thread1.join();
        thread2.join();
        System.out.println("finish batch " + i);
    }
    connection1.close();
    connection2.close();
    System.out.println("finish all");

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
to-reproduce
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@aneasystone @jianhong-li @804e @humanbeingxx
0