Chinaunix首页 | 论坛 | 博客
  • 博客访问: 373323
  • 博文数量: 55
  • 博客积分: 3195
  • 博客等级: 中校
  • 技术积分: 712
  • 用 户 组: 普通用户
  • 注册时间: 2010-07-01 18:45
文章分类

全部博文(55)

文章存档

2011年(14)

2010年(41)

分类: Mysql/postgreSQL

2010-08-02 22:26:04

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

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

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

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)

阅读(1855) | 评论(0) | 转发(2) |
给主人留下些什么吧!~~