精华内容
下载资源
问答
  • Mysql Innodb死锁解决

    千次阅读 2017-02-07 11:00:19
    mysql innodb 死锁
    • 场景:在操作数据库的过程中,由于一些原因,如:事务未正常提交/回滚等造成数据库死锁,导致其他需要事务的操作出现锁等待超时的错误,Lock wait timeout exceeded; try restarting transaction。

    • 查询数据库死锁的线程id:

      SELECT * FROM information_schema.INNODB_TRX\G
    • 查询结果如下:

      mysql> SELECT * FROM information_schema.INNODB_TRX\G
      
      ************************** 1. row **************************
      
      trx_id: 189324
      
      trx_state: RUNNING
      
      trx_started: 2017-02-17 10:00:00
      
      trx_requested_lock_id: NULL
      
      trx_wait_started: NULL
      
      trx_weight: 3
      
      trx_mysql_thread_id: 5
      
      trx_query: NULL
      
      trx_operation_state: NULL
      
      trx_tables_in_use: 0
      
      trx_tables_locked: 0
      
      trx_lock_structs: 2
      
      trx_lock_memory_bytes: 376
      
      trx_rows_locked: 3
      
      trx_rows_modified: 1
      
      trx_concurrency_tickets: 0
      
      trx_isolation_level: REPEATABLE READ
      
      trx_unique_checks: 1
      
      trx_foreign_key_checks: 1
      
      trx_last_foreign_key_error: NULL
      
      trx_adaptive_hash_latched: 0
      
      trx_adaptive_hash_timeout: 10000
      
      trx_is_read_only: 0
      
      trx_autocommit_non_locking: 0
      
      1 row in set (0.01 sec)
    • 杀掉死锁线程:

      kill 5;
    展开全文
  • mysql innodb 死锁

    2013-09-25 15:49:46
    mysql innodb 死锁 2011-03-21 10:14:02 分类: Mysql/postgreSQL innodb大量AUTO-INCLOCK 导致的DeadLOCK1.现象 今天发现数据库报检查到死锁, show innodb status \G但从STATUS看,都是 lock mode AUTO-...
    mysql innodb 死锁
    2011-03-21 10:14:02
    

    分类: Mysql/postgreSQL

    innodb大量AUTO-INCLOCK 导致的DeadLOCK1.现象

    今天发现数据库报检查到死锁, show innodb status \G但从STATUS看,都是 lock mode AUTO-INC waiting.而且从应用开发了解,都是简单的INSERT和select。 没有交叉更新的事务。 

    如果是等待AUTO-INC锁,也不应该会产生死锁。因为AUTO-INC锁在某一时刻只会被一个事务/INSERT操作占有。INSERT一旦完成就会释放AUTO-INC锁。

    那怎么会有死锁呢?

    2.原因

    今天查了一下资料,大致了解了当innodb_autoinc_lock_mode=0(mysql5.1.22之前连这个选项都没添加,所以默认都是0)时,在并发数大于208以上可能出现很多死锁的原因,如下:

     

    在innodb源代码lock/lock0lock.c文件中,定义了两个常量:

     

    /* Restricts thelength of search we will do in the waits-for

         graph of transactions */

    #defineLOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK 1000000

     

    /* Restricts therecursion depth of the search we will do in the waits-for

        graph of transactions */

    #defineLOCK_MAX_DEPTH_IN_DEADLOCK_CHECK 200

     

    然后在检查是否产生死锁的函数lock_deadlock_occurs()中有如下代码:

     

    ret =lock_deadlock_recursive(trx, trx, lock, &cost, 0);

    switch (ret) {

    caseLOCK_EXCEED_MAX_DEPTH:

            产生死锁

            ...

            break;

    }

    其中的lock_deadlock_recursive()函数是递归函数,它会检查自身递归深度,其中有如下代码:

     

    ibool   too_far

       = depth >LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK

    ||*cost > LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK;

    ...

    if (too_far) {

                    return(LOCK_EXCEED_MAX_DEPTH);

                }

    因此innodb在检查是否产生死锁时调用lock_deadlock_occurs()检查,这个函数再会调用lock_deadlock_recursive()递归检查锁的数目(不知道这么说是否确切?),当递归的深度depth大于了一开始介绍的常量LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK,或者cost(不清楚这个代表什么)大于一开始介绍的常量LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK时,就认为发生了死锁.

    当并发数超过208的时候,发生了很多死锁,基于上面的分析我们可以得知,因为每一个并发做的是一个insert操作,需要加一个锁(暂且不管是s锁还是x锁),200多个线程并发执行,第n个insert正在执行,后来的线程无法获得锁,于是加入到lockqueue中排队等待,这个时候lockqueue长度超过了常量LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK所默认定义的200,然后Innodb在检查是否产生死锁的时候发现在递归检查lock_queue的时候深度超过了200,于是就认为发生了死锁.

    废话了这么多就是当并发数超过208的时候mysql认为发生了死锁,因为AUTO-INC锁在mysql 5.1.22前,这个是表级锁,如果并发高、数据量大的话,很容易就到达208的

    3.防止

    找到原因后,下面就是怎么防止这个死锁的产生呢?

    上面的测试也说了,在mysql 5.1.22中,已经改良了Innodb的auto_increment的锁机制,增加了一个innodb_autoinc_lock_mode选项,我们只需要使用其默认值1就可以很好的避免这个问题.

    3.1 INSERT-like

    在mysql5.1.22之前,mysql的“INSERT-like”语句(包INSERT,INSERT…SELECT, REPLACE,REPLACE…SELECT, and LOAD DATA)会在执行整个语句的过程中使用一个AUTO-INC锁将表锁住,直到整个语句结束(而不是事务结束)。因此在使用INSERT…SELECT、INSERT…values(…),values(…)时,LOADDATA等耗费时间较长的操作时,会将整个表锁住,而阻塞其他的“INSERT-like”、Update等语句,推荐使用程序将这些语句分成多条语句,一一插入,减少单一时间的锁表时间。

    mysql5.1.22之后mysql进行了改进,引入了参数innodb_autoinc_lock_mode,通过这个参数控制mysql的锁表逻辑,在介绍这个之前先引入几个术语,方便说明innodb_autoinc_lock_mode。

    INSERT-like:

    INSERT, INSERT …SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()

     

    Simpleinserts

    就是通过分析insert语句可以确定插入数量的insert语句,INSERT, INSERT … VALUES(),VALUES()

     

    Bulkinserts

    就是通过分析insert语句不能确定插入数量的insert语句,INSERT … SELECT, REPLACE … SELECT, LOAD DATA

     

    Mixed-modeinserts

    下面两种,不确定是否需要分配auto_incrementid

    INSERTINTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');

    INSERT… ON DUPLICATE KEY UPDATE

    3.2 innodb_autoinc_lock_mode选项3.2.1 innodb_autoinc_lock_mode = 0(“traditional” lock mode)

    这种方式就和mysql5.1.22以前一样,为了向后兼容而保留了这种模式,如同前面介绍的一样,这种方式的特点就是“表级锁定”,并发性较差

    3.2.2 innodb_autoinc_lock_mode = 1(“consecutive” lock mode)

    这种方式是新版本中的默认方式,推荐使用,并发性相对较高,特点是“consecutive”,即保证同一条insert语句中新插入的auto_incrementid都是连续的。

     

    这种模式下:

    “Simple inserts”:直接通过分析语句,获得要插入的数量,然后一次性分配足够的auto_incrementid,只会将整个分配的过程锁住。

     

    “Bulk inserts”:因为不能确定插入的数量,因此使用和以前的模式相同的表级锁定。

     

    “Mixed-modeinserts”:直接分析语句,获得最坏情况下需要插入的数量,然后一次性分配足够的auto_incrementid,只会将整个分配的过程锁住。需要注意的是,这种方式下,会分配过多的id,而导致”浪费“。比如INSERTINTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');会一次性的分配5个id,而不管用户是否指定了部分id;INSERT … ONDUPLICATE KEY UPDATE一次性分配,而不管将来插入过程中是否会因为duplicatekey而仅仅执行update操作。

    注意:当master mysql版本<5.1.22,slave mysql版本>=5.1.22时,slave需要将innodb_autoinc_lock_mode设置为0,因为默认的innodb_autoinc_lock_mode为1,对于INSERT… ON DUPLICATE KEY UPDATE和INSERTINTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');的执行结果不同,现实环境一般会使用INSERT… ON DUPLICATE KEY UPDATE。

    3.2.3 innodb_autoinc_lock_mode = 2(“interleaved” lock mode)

    这种模式是来一个分配一个,而不会锁表,只会锁住分配id的过程,和innodb_autoinc_lock_mode= 1的区别在于,不会预分配多个,这种方式并发性最高。但是在replication中当binlog_format为statement-based时(简称SBRstatement-based replication)存在问题,因为是来一个分配一个,这样当并发执行时,“Bulkinserts”在分配的时会同时向其他的INSERT分配,会出现主从不一致(从库执行结果和主库执行结果不一样),因为binlog只会记录开始的insertid。

     

    测试SBR,执行begin;insertvalues(),();insert values(),();commit;会在binlog中每条insertvalues(),();前增加SETINSERT_ID=18/*!*/;。

     

    但是row-basedreplication RBR时不会存在问题。

     

    另外RBR的主要缺点是日志数量在包括语句中包含大量的updatedelete(update多条语句,delete多条语句)时,日志会比SBR大很多;假如实际语句中这样语句不是很多的时候(现实中存在很多这样的情况),推荐使用RBR配合innodb_autoinc_lock_mode,不过话说回来,现实生产中“Bulkinserts”本来就很少,因此innodb_autoinc_lock_mode= 1应该是够用了。
    展开全文
  • mysql InnoDB死锁

    2020-04-02 13:18:58
    MyISAM表锁时deadlock ...但InnoDB,除单个SQL组成的事务外,锁是逐步获得的,所以会发生死锁的可能。 session1 session2 select * from table1 where id = 1 for update; select * from table2 wh...

    MyISAM表锁时deadlock free的,因为MyISAM总是一次获得所需要的全部锁,不会出现死锁。

    但InnoDB,除单个SQL组成的事务外,锁是逐步获得的,所以会发生死锁的可能。

    session1 session2

    select * from table1 where id = 1 for update;

    select * from table2 where id = 1 for update;

    select * from table2 where id = 1 for update;

    因为session2已取得排他锁,等待

     
     

    select * from table1 where id = 1 for update;

    死锁

     

    2个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等待就是常见的死锁。

     

    避免死锁常用方法

    1、在应用种,如果不同的程序会并发存取多个表,应尽量约定以相同顺序来访问表。

    2、在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定顺序来处理记录,也可以降低死锁的可能。

    3、在事务种,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不允许申请共享锁,更新时再申请排他锁。

     

    展开全文
  • MySQL InnoDB 死锁问题

    2021-01-25 09:03:14
    InnoDB中,除单个SQL组成的事务外,锁是逐步获得的,这就决定了在InnoDB中发生死锁是可能的。 InnoDB 存储引擎中的死锁例子 session_1 session_2 mysql> set autoco.

    关于死锁

    1、为什么MyISAM表锁不会出现死锁

    MyISAM表锁是deadlock free的,这是因为MyISAM总是一次获取所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。

    2、为什么InnoDB行锁会出现死锁

    在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的,这就决定了在InnoDB中发生死锁是可能的。

     InnoDB 存储引擎中的死锁例子

    session_1

    session_2

    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select * from table_1 where where id=1 for update;
    ...
    做一些其他处理...

    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select * from table_2 where id=1 for update; ...

    select * from table_2 where id =1 for update; 因 session_2 已取得排他锁,等待

    做一些其他处理...

     

    mysql> select * from table_1 where where id=1 for update;
    死锁

    在上面的例子中,两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等 待就是典型的死锁。
    发生死锁后,InnoDB 一般都能自动检测到,并使一个事务释放锁并回退,另一个事务获得 锁,继续完成事务。但在涉及外部锁,或涉及表锁的情况下,InnoDB 并不能完全自动检测 到死锁,这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。需要说明的是, 这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立 即获得所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖跨数据库。我 们通过设置合适的锁等待超时阈值,可以避免这种情况发生。通常来说,死锁都是应用设计的问题,通过调整业务流程、数据库对象设计、事务大小,以 及访问数据库的 SQL 语句,绝大部分死锁都可以避免。

    下面就通过实例来介绍几种避免死 锁的常用方法。

    (1)在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问 表,这样可以大大降低产生死锁的机会。在下面的例子中,由于两个 session 访问两个表的 顺序不同,发生死锁的机会就非常高!但如果以相同的顺序来访问,死锁就可以避免。

     InnoDB 存储引擎中表顺序造成的死锁例子

    session_1

    session_2

    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select first_name,last_name from actor where actor_id = 1 for update; +------------+-----------+
    | first_name | last_name | +------------+-----------+

    | PENELOPE | GUINESS | +------------+-----------+ 1 row in set (0.00 sec)

     
     

    mysql> insert into country (country_id,country) values(110,'Test'); Query OK, 1 row affected (0.00 sec)

    mysql> insert into country

     

    (country_id,country) values(110,'Test'); 等待

     
     

    mysql> select first_name,last_name from actor where actor_id = 1 for update; +------------+-----------+
    | first_name | last_name | +------------+-----------+

    | PENELOPE | GUINESS | +------------+-----------+ 1 row in set (0.00 sec)

    mysql> insert into country (country_id,country) values(110,'Test'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

     

    (2)在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定 的顺序来处理记录,也可以大大降低出现死锁的可能。

     InnoDB 存储引擎中表数据操作顺序不一致造成的死锁例子

    session_1

    session_2

    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select first_name,last_name from actor where actor_id = 1 for update; +------------+-----------+
    | first_name | last_name | +------------+-----------+

    | PENELOPE | GUINESS | +------------+-----------+ 1 row in set (0.00 sec)

     
     

    mysql> select first_name,last_name from actor where actor_id = 3 for update; +------------+-----------+
    | first_name | last_name | +------------+-----------+

    | ED | CHASE | +------------+-----------+ 1 row in set (0.00 sec)

    mysql> select first_name,last_name from actor where actor_id = 3 for update;
    等待

     
     

    mysql> select first_name,last_name from actor where actor_id = 1 for update;
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

    mysql> select first_name,last_name from actor where actor_id = 3 for update; +------------+-----------+
    | first_name | last_name | +------------+-----------+

    | ED | CHASE | +------------+-----------+ 1 row in set (4.71 sec)

     

    (3)在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请 共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同 记录的共享锁,从而造成锁冲突,甚至死锁。

    InnoDB 存储引擎的共享锁例子

    session_1

    session_2

    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+
    | actor_id | first_name | last_name | +----------+------------+-----------+

    | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.00 sec)

    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+
    | actor_id | first_name | last_name | +----------+------------+-----------+

    | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.00 sec)

    当前 session 对 actor_id=178 的记录加 share mode 的共享锁:
    mysql> select actor_id,first_name,last_name from actor where actor_id = 178 lock in share mode;

    +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+

     

    | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.01 sec)

     
     

    其他session仍然可以查询记录,并也可以对该记 录加 share mode 的共享锁:
    mysql> select actor_id,first_name,last_name from actor where actor_id = 178 lock in share mode;

    +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.01 sec)

    当前 session 对锁定的记录进行更新操作,等待锁: mysql> update actor set last_name = 'MONROE T' where actor_id = 178;
    等待

     
     

    其他 session 也对该记录进行更新操作,则会导致 死锁退出:
    mysql> update actor set last_name = 'MONROE T' where actor_id = 178;

    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

    获得锁后,可以成功更新:
    mysql> update actor set last_name = 'MONROE T' where actor_id = 178;
    Query OK, 1 row affected (17.67 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

     

    当使用SELECT...FOR UPDATE加锁后再更新记录。

    InnoDB存储引擎的排他锁例子

    session_1

    session_2

    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+
    | actor_id | first_name | last_name | +----------+------------+-----------+

    | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.00 sec)

    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+
    | actor_id | first_name | last_name | +----------+------------+-----------+

    | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.00 sec)

    当前 session 对 actor_id=178 的记录加 for update 的共享锁:
    mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update; +----------+------------+-----------+

    | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.00 sec)

     
     

    其他 session 可以查询该记录,但是不能对该记录 加共享锁,会等待获得锁:
    mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+

    | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE | +----------+------------+-----------+ 1 row in set (0.00 sec)

    mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update; 等待

    当前session可以对锁定的记录进行更新操作,更新 后释放锁:
    mysql> update actor set last_name = 'MONROE T' where actor_id = 178;

    Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

    mysql> commit;
    Query OK, 0 rows affected (0.01 sec)

     
     

    其他 session 获得锁,得到其他 session 交的记 录:
    mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update; +----------+------------+-----------+

    | actor_id | first_name | last_name | +----------+------------+-----------+ | 178 | LISA | MONROE T | +----------+------------+-----------+ 1 row in set (9.59 sec)

    (4)在 REPEATABLE-READ 隔离级别下,如果两个线程同时对相同条件记录 用 SELECT...FOR UPDATE 加排他锁,在没有符合该条件记录情况下,两个线程都会加锁成功。 程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。 这种情况下,将隔离级别改成 READ COMMITTED,就可避免问题,如表 20-20 所示。
    InnoDB 存储引擎中隔离级别引起的死锁例子

    session_1

    session_2

    mysql> select @@tx_isolation; +-----------------+
    | @@tx_isolation | +-----------------+

    | REPEATABLE-READ | +-----------------+
    1 row in set (0.00 sec)

    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select @@tx_isolation; +-----------------+
    | @@tx_isolation | +-----------------+

    | REPEATABLE-READ | +-----------------+
    1 row in set (0.00 sec)

    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    当前 session 对不存在的记录加 for update 的锁: mysql> select actor_id,first_name,last_name from actor where actor_id = 201 for update; Empty set (0.00 sec)

     
     

    其他 session 也可以对不存在的记录加 for update 的锁:
    mysql> select actor_id,first_name,last_name from actor where actor_id = 201 for update; Empty set (0.00 sec)

    因为其他session也对该记录加了锁,所以当前的插 入会等待:
    mysql> insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom');

    等待

     
     

    因为其他session已经对记录进行了更新,这时候

     

    再插入记录就会 示死锁并退出:
    mysql> insert into actor (actor_id, first_name , last_name) values(201,'Lisa','Tom');
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

    由于其他session已经退出,当前session可以获得 锁并成功插入记录:
    mysql> insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom');

    Query OK, 1 row affected (13.35 sec)

     

    (5)隔离级别为 READ COMMITTED 时,如果两个线程都先执行 SELECT...FOR UPDATE,判 断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另 一个线程会出现锁等待,当第 1 个线程 交后,第 2 个线程会因主键重出错,但虽然这个线 程出错了,却会获得一个排他锁!这时如果有第 3 个线程又来申请排他锁,也会出现死锁。

    对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误 时,总是执行 ROLLBACK 释放获得的排他锁。

     InnoDB 存储引擎中隔离级别引起的死锁例子

    session_1

    session_2

    session_3

    mysql> select @@tx_isolation; +----------------+
    | @@tx_isolation | +----------------+

    | READ-COMMITTED | +----------------+
    1 row in set (0.00 sec)

    mysql> set autocommit=0; Query OK, 0 rows affected (0.01 sec)

    mysql> select @@tx_isolation; +----------------+
    | @@tx_isolation | +----------------+

    | READ-COMMITTED | +----------------+
    1 row in set (0.00 sec)

    mysql> set autocommit=0; Query OK, 0 rows affected (0.01 sec)

    mysql> select @@tx_isolation; +----------------+
    | @@tx_isolation | +----------------+

    | READ-COMMITTED | +----------------+
    1 row in set (0.00 sec)

    mysql> set autocommit=0; Query OK, 0 rows affected (0.01 sec)

    Session_1 获得 for update 的共享 锁:
    mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update;

    Empty set (0.00 sec)

    由于记录不存在,session_2 也可 以获得for update的共享锁: mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update;

    Empty set (0.00 sec)

     

    Session_1 可以成功插入记录: mysql> insert into actor (actor_id,first_name,last_nam e) values(201,'Lisa','Tom'); Query OK, 1 row affected (0.00 sec)

       
     

    Session_2 插入申请等待获得锁: mysql> insert into actor (actor_id,first_name,last_nam e) values(201,'Lisa','Tom'); 等待

     

    Session_1 成功 交:
    mysql> commit;
    Query OK, 0 rows affected (0.04 sec)

       
     

    Session_2 获得锁,发现插入记录 主键重,这个时候抛出了异常,但 是并没有释放共享锁:
    mysql> insert into actor (actor_id,first_name,last_nam e) values(201,'Lisa','Tom'); ERROR 1062 (23000): Duplicate entry '201' for key 'PRIMARY'

     
       

    Session_3 申请获得共享锁,因为 session_2 已经锁定该记录,所以 session_3 需要等待:
    mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update;

    等待

     

    这个时候,如果 session_2 直接对 记录进行更新操作,则会抛出死锁 的异常:
    mysql> update actor set last_name='Lan' where actor_id = 201;

    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

     
       

    Session_2 释放锁后,session_3 获得锁:
    mysql> select first_name, last_name from actor where actor_id = 201 for update; +------------+-----------+

    | first_name | last_name | +------------+-----------+ | Lisa | Tom | +------------+-----------+

       

    1 row in set (31.12 sec)

    尽管通过上面介绍的设计和 SQL 优化等措施,可以大大减少死锁,但死锁很难完全避 免。因此,在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。

    如果出现死锁,可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返 回结果中包括死锁相关事务的详细信息,如引发死锁的 SQL 语句,事务已经获得的锁,正 在等待什么锁,以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

    ------------------------------------------------------------------------------------------------------------------

    注:以上内容摘自《深入浅出MySQL》

    展开全文
  • mysql innoDB 死锁

    2020-07-10 14:44:10
    一、什么是死锁 官方定义如下:两个事务都持有对方需要的锁,并且在等待对方释放,并且双方都不会释放自己的锁。 这个就好比你有一个人质,对方有一个人质,你们俩去谈判说换人。你让对面放人,对面让你放人。 ...
  • 数据库也会发生死锁的现象,数据库系统实现了各种死锁检测和死锁超时机制来解除死锁,锁监视器进行死锁检测,MySQLInnoDB处理死锁的方式是 将持有最少行级排它锁的事务进行回滚,相对比较简单的死锁回滚办法
  • 该文档详细分析了innodb的加锁原理、死锁原因以及处理方式
  • mysql innodb 死锁分析

    2016-12-20 23:54:16
    mysqlVer14.14Distrib5.7.16,forlinux-glibc2.5(x86_64)usingEditLinewrapper#mysql版本5.7.16 Connectionid:10042 Currentdatabase:china9129 Currentuser:root@localhost SSL:Notin...
  • 记一次innodb死锁分析
  • mysql innodb死锁问题详解

    千次阅读 2018-01-12 11:00:17
    一 首先发现问题 1 最近旅游电商平台对外提供的接口经常有终端用户反映请求超时异常 。 2 进过排查服务器日志有报错,错误信息如下: ...3 了解平台使用的是mysql 数据库版本5.6.34 存储引擎是innodb。 4 推
  • 14.5.5.1 InnoDB死锁示例 以下示例说明了锁请求如何发生错误会导致死锁时。这个例子涉及两个客户端,A和B。 首先,客户端A创建一个包含一行的表,然后开始一个事务。在事务中,A通过在共享模式下select来获得行上...
  • 写的很不错,收藏并分享之   http://hi.baidu.com/baiduqa/blog/item/c53b8c95663afd7855fb9697.html
  • mysqlinnodb死锁问题

    千次阅读 2014-04-03 19:54:25
     线上生产环境在某些时候经常性的出现数据库操作死锁,导致业务人员无法进行操作。经过DBA的分析,是某一张表的insert操作和delete操作发生了死锁。简单介绍下数据库的情况(因为涉及到真实数据,这里做了模拟,不...
  • Mysql Innodb死锁情况分析与归纳

    千次阅读 2013-09-30 16:53:12
     在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志。  两个sql语句如下:  (1)insert into backup_table select * from source_table ...
  • NULL 博文链接:https://edwin-chen.iteye.com/blog/440645
  • Innodb 死锁解析

    2019-12-18 16:59:31
    近期查看线上日志,发现sql某个update语句每日...com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction 进一步分析日志,原因是...
  • 这几天在研究innodb死锁问题,翻了下《mysql技术内幕:innodb存储引擎》,原来在mysql内部,已经有三个表记录了事务锁的相关信息。记录下来,防止忘了 ...................................... information_...
  • 在数据库系统中,死锁问题几乎是不可...在MySQL InnoDB引擎中,死锁的检测是通过深度遍历进行的,每一个需要等待的行锁请求都需要去检测是否可能产生死锁。 关于InnoDB事务锁,可以参阅我之前的一篇博客,这里不展...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,767
精华内容 706
关键字:

innodb死锁mysql

mysql 订阅