mysql事务与锁

一、事务（Transaction）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性：
1.原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
2.一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
3.隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
4.持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

二、并发事务处理带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。
1.更新丢失（Lost Update）：
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。
2.脏读（Dirty Reads）：
一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。
3.不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
4.幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

三、事务隔离级别

在上面讲到的并发事务处理带来的问题中，“更新丢失”通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本上可分为以下两种：
1.在读取数据前，对其加锁，阻止其他事务对数据进行修改。
2.不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了4个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己的业务逻辑要求，通过选择不同的隔离级别来平衡 “隔离”与“并发”的矛盾。下表很好地概括了这4个隔离级别的特性：

Mysql锁：

MySQL 支持对 MyISAM 和 MEMORY 表进行表级锁定，对 InnoDB 表进行行级锁定。

在许多情况下，可以根据猜测应用程序使用哪类锁定类型最好，但一般很难说出某个给出的锁类型就比另一个好。一切取决于应用程序，应用程序的不同部分可能需要不同的锁类型。

为了确定是否想要使用行级锁定的存储引擎，应看看应用程序做什么并且混合使用什么样的选择和更新语句。例如，大多数Web应用程序执行许多选择，而很少进行删除，只对关键字的值进行更新，并且只插入少量具体的表。基本MySQL MyISAM 设置已经调节得很好。

在 MySQL 中对于使用表级锁定的存储引擎，表锁定时不会死锁的。这通过总是在一个查询开始时立即请求所有必要的锁定并且总是以同样的顺序锁定表来管理。

对 WRITE，MySQL使用的表锁定方法原理如下：
 如果在表上没有锁，在它上面放一个写锁。
 否则，把锁定请求放在写锁定队列中。

对 READ，MySQL使用的锁定方法原理如下：
 如果在表上没有写锁定，把一个读锁定放在它上面。
 否则，把锁请求放在读锁定队列中。

当一个锁定被释放时，锁定可被写锁定队列中的线程得到，然后是读锁定队列中的线程。

这意味着，如果你在一个表上有许多更新，SELECT语句将等待直到没有更多的更新。

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定争夺：

mysql> show global status like "table_locks%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 36    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

Table_locks_immediate 发生表锁定操作, 但表锁定后马上释放
Table_locks_waited  发生表锁定, 并因此具有锁等待

对于 SQL 执行过程中, 需要对 SQL 进行硬解析, 计算 SQL 的运行成本后得到执行计划, 在执行 SQL 语句的时候需要查询当前资源是否具有锁机制, 如果当前资源被锁定中, 必须等待资源释放后才能够继续执行 SQL.

利用 SCHEMA sbtest.new 作为测试对象. 参考下面表结构。

mysql> desc sbtest.new;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

 

下面两个例子举例说明锁定的发生。

例子 1 读锁定

当前具有两个 session 登录到 MySQL 服务器中, 简称 sessionA 与 sessionB。

sessionA 中执行下面操作。

sessionA> show global status like "table_locks%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 36    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

解释：查询当前 MySQL 中表锁定信息。

sessionA> lock table sbtest.new write;
Query OK, 0 rows affected (0.00 sec)

解释：对测试表 sbtest.new 锁定，该操作只会影响其他会话对 sbtest.new 表执行 DDL 及 DML 操作。

sessionA> show global status like "table_locks%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 37    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

解释：当执行 lock table 操作之后，系统会对 sbtest.new 表执行一次锁定操作，当完成在表中数据库头部标记锁定资源操作后，释放锁。

在当前 sessionA 执行锁定操作状态下，不影响 sessionA 对表 sbtest.new 进行增删改操作，参考例子。

sessionA> insert into sbtest.new values (4),(5),(6);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

sessionA> select * from sbtest.new;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0.00 sec)

sessionA> delete from sbtest.new where id > 3;
Query OK, 3 rows affected (0.06 sec)

完成上述操作后， 切换到 sessionB 会话中， 执行下面操作。

sessionB> select * from sbtest.new;

解释：当 sessionB 进行表查询时，由于 sessionA 执行锁定操作，导致查询等待，直到锁定结束为止。

利用管理员创建 sessionC 登录到 MySQL, 利用 show processlist 命令显示当前登录到 MySQL 终端的所有状态状态信息。

sessionC> show processlist;
+----+------+---------+------+---------------------------------+--------------------------+
| Id | db   | Command | Time | State                           | Info                     |
+----+------+---------+------+---------------------------------+--------------------------+
|  1 | NULL | Sleep   |  120 |                                 | NULL                     |
|  2 | NULL | Query   |    0 | NULL                            | show processlist         |
|  3 | NULL | Query   |  112 | Waiting for table metadata lock | select * from sbtest.new |
+----+------+---------+------+---------------------------------+--------------------------+
3 rows in set (0.00 sec)

要使用 show processlist 必须具有 PROCESS 权限，由于排版关系，返回信息进行部分折断，从 State 状态栏中可以清楚看到， ID 3 的会话当前正在处于查询等待状态， Waiting for table metadata lock 显示当前等待状态信息。

 

只要 sessionA 对表执行解锁操作，sessionB 就能够重新获得资源，继续之前 SQL 操作。

sessionA> unlock tables;
Query OK, 0 rows affected (0.00 sec)

sessionB> select * from sbtest.new;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (29 min 52.91 sec)

例子 2 死锁

MySQL 在使用 InnoDB 引擎时，默认使用自动提交机制，该机制能够自动帮我们完成事务，自动提交机制并不能够为我们提高事务性能，我们稍后进行描述。

认识一下死锁的发生。我们需要两个会话，分别是 sessionA 与 sessionB。分别创建两个表，t_lock.a 与 t_lock.b, t_lock.a 与 t_lock.b 结构一致，如下描述。

mysql> desc t_lock.a;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc t_lock.b;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

测试过程中，sessionA 与 sessionB 需要关闭自动提交功能。
1. sessionA 登录到数据库，分别在两个表中插入测试数据。

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

sessionA> insert into t_lock.a values (1,'from sessionA');
Query OK, 1 row affected, 1 warning (0.00 sec)

sessionA> insert into t_lock.b values (1,'from sessionA');
Query OK, 1 row affected, 1 warning (0.00 sec)

sessionA> commit;
Query OK, 0 rows affected (0.04 sec)

2. sessionA 更新 A 表中数据，不提交事务。

sessionA> update t_lock.a set name='new data' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

sessionA> select * from t_lock.a;
+----+----------+
| id | name     |
+----+----------+
|  1 | new data |
+----+----------+
1 row in set (0.00 sec)

3. sessionB 更新 B 表数据， 再更新 A 表数据，由于当前 A 表数据被 sessionA 修改中，数据处于保护状态，导致 sessionB 尝试修改 A 表数据时候出现锁定等待。锁定过程中，系统显示进程为更新中。

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

mysql> update t_lock.b set name='update data' where id=1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

sessionB> select * from t_lock.b;
+----+------------+
| id | name       |
+----+------------+
|  1 | update dat |
+----+------------+
1 row in set (0.00 sec)

sessionB> update t_lock.a set name='update data' where id=1;

sessionA> show full processlist;
+----+------+---------+------+----------+---------------------------------------------------+
| Id | db   | Command | Time | State    | Info                                              |
+----+------+---------+------+----------+---------------------------------------------------+
|  2 | NULL | Query   |    8 | Updating | update t_lock.a set name='update data' where id=1 |
|  3 | NULL | Query   |    0 | NULL     | show full processlist                             |
+----+------+---------+------+----------+---------------------------------------------------+
2 rows in set (0.00 sec)

4. 这个时候当 sessionA 尝试修改 B 表数据，因为 sessionB 当前为锁定状态，而且 sessionB 对 B 表中数据具有锁定状态中，则出现死锁。sessionB 会自动终止尝试修改 A 表数据事务， 并返回下面错误信息。而且下面两个事务操作都被终止。

sessionA> update t_lock.b set name='new data' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

sessionB> update t_lock.a set name='update data' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

sessionA> select * from t_lock.a;
+----+----------+
| id | name     |
+----+----------+
|  1 | new data |
+----+----------+
1 row in set (0.00 sec)

sessionB> select * from t_lock.b;
+----+------------+
| id | name       |
+----+------------+
|  1 | update dat |
+----+------------+
1 row in set (0.00 sec)

转载地址:http://blog.csdn.net/signmem/article/details/7689157