数据库事务处理之排他锁_MySQL

bitsCN.com

数据库事务处理之排他锁

排他锁    

下面做作一个实验，验证锁的效果

终端一,首先进入事务状态然后运行下面语句

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id='3' for update;

+----+--------+---------------------+---------------------+

| id | name   | ctime               | mtime               |

+----+--------+---------------------+---------------------+

|  3 | test   | 0000-00-00 00:00:00 | 2013-01-14 13:05:41 |

+----+--------+---------------------+---------------------+

1 row in set (0.00 sec)

终端二, 查询表中数据

mysql> select * from t1;

+----+--------+---------------------+---------------------+

| id | name   | ctime               | mtime               |

+----+--------+---------------------+---------------------+

|  1 | neo    | 0000-00-00 00:00:00 | 2013-01-14 13:00:00 |

|  2 | zen    | 0000-00-00 00:00:00 | 2013-01-14 13:00:43 |

|  3 | test   | 0000-00-00 00:00:00 | 2013-01-14 13:05:41 |

+----+--------+---------------------+---------------------+

3 rows in set (0.00 sec)

增加“for update”查询非锁定记录    

mysql> select * from t1 where id=2 for update;

+----+------+---------------------+---------------------+

| id | name | ctime               | mtime               |

+----+------+---------------------+---------------------+

|  2 | zen  | 0000-00-00 00:00:00 | 2013-01-14 13:00:43 |

+----+------+---------------------+---------------------+

1 row in set (0.00 sec)

查询被锁定记录

mysql> select * from t1 where id=3 for update;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

查询所有记录，因为记录中包含了id=3那条，所以也不允许查询。

mysql> select * from t1 for update;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

测试修改记录

mysql> UPDATE `t1` SET `name`='testaa' WHERE  `id`=3;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

提示

在没有出现ERROR 1205 (HY000)的这段时间，只要终端一中执行commit,rollback.终端二中的语句就会运行。