Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1087137
  • 博文数量: 321
  • 博客积分: 7872
  • 博客等级: 少将
  • 技术积分: 2120
  • 用 户 组: 普通用户
  • 注册时间: 2007-05-16 09:06
文章分类

全部博文(321)

文章存档

2017年(1)

2016年(1)

2015年(12)

2014年(17)

2013年(78)

2012年(15)

2011年(17)

2010年(67)

2009年(102)

2008年(11)

分类: LINUX

2009-07-29 13:30:10

 2008-03-27 23:07  唐汉明 翟振兴 兰丽华 关宝军 申宝柱  人民邮电出版社  
  • 摘要:《深入浅出MySQL——数据库开发、优化与管理维护》从数据库的基础、开发、优化、管理4方面对MySQL进行了详细的介绍,其中每一部分都独立成篇,每一篇又包括多个章节。本书面向实用,内容覆盖广泛,讲解由浅入深,适合于各个层次的读者。本文介绍了InnoDB锁。
  • 标签:    

20.3.9 关于死锁

上文讲过,MyISAM表锁是deadlock free的,这是因为MyISAM总是一次获得所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。但在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的,这就决定了在InnoDB中发生死锁是可能的。如表20-17所示的就是一个发生死锁的例子。

表20-17           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访问两个表的顺序不同,发生死锁的机会就非常高!但如果以相同的顺序来访问,死锁就可以避免。

表20-18  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)在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。

表20-19  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)在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。具体演示可参见20.3.3小节中的例子。

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

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

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释放获得的排他锁,如表20-21所示。

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

_name) values(201,'Lisa','Tom');

Query OK, 1 row affected (0.00 sec)

 

 

 

Session_2插入申请等待获得锁:

mysql> insert into actor (actor_id,first_name,

last_name) 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_name) 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语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。下面是一段SHOW INNODB STATUS输出的样例:

mysql> show innodb status \G
…….
------------------------
LATEST DETECTED DEADLOCK
------------------------
070710 14:05:16
*** (1) TRANSACTION:
TRANSACTION 0 117470078, ACTIVE 117 sec, process no 1468, OS
thread id 1197328736 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1216
MySQL thread id 7521657, query id 673468054 localhost root update
insert into country (country_id,country) values(110,'Test')
………
*** (2) TRANSACTION:
TRANSACTION 0 117470079, ACTIVE 39 sec, process no 1468, OS
thread id 1164048736 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 7521664, query id 673468058 localhost root statistics
select first_name,last_name from actor where actor_id = 1 for update
*** (2) HOLDS THE LOCK(S):
………
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
………
*** WE ROLL BACK TRANSACTION (1)
……
阅读(936) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~