Mysql 事务与锁-实例分析

为了加深对mysql事务和锁的认识，模拟几个场景加深印象。

  为了方便控制事务，先设置客户端事务非自动提交：

• 查看下当前事务提交状态 （1 自动提交；0 手动）| @@ 代表系统变量

    select @@autocommit；/ show variables like '%autocommit%';

• 设置为手动提交

     set @@autocommit=0；

 

开始模拟几个场景，并作如下记录：

1. 开启两个事物，update 同一条数据 （X 互斥锁，导致 wait lock）

• 事务1：tx1

• 事务2：tx2

• 查看事务 INNODB_TR 表  

     select * from information_schema.INNODB_TRX; 

      下图中事务tx2 的状态为 LOCK_WAIT ,等待 事务tx1 RUNNIING 中持有的锁。

 

• 查看锁类型 INNODB_LOCKS 表

    select * from information_schema.INNODB_LOCKS;

     下图中两条 update语句都加 x 锁（排他锁）。update 默认都是互斥x锁，当记录加X锁时，其他事务不允许对此数据集加其他锁，X 或 S锁都不允许，所以事务tx2 需要等待 tx1 释放锁（commit）之后，才可以对数据集加锁。

 

• 导致问题 Lock wait timeout

    事务tx2 等待锁时间超过默认 50s，会报错 Lock wait timeout exceeded。

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

     

  2. 开启多个事务, SELECT 显示加锁 ，

• 显示加 X 互斥锁

      * 开始事务 tx1 ，select 语句通过 for update 显示加 X 锁

   * 开始事务tx2/tx3，select 语句通过 for update / lock in share mode  显示加 X锁 / S 锁 

  *  select * from information_schema.INNODB_TRX;
      select * from information_schema.INNODB_LOCKS;

     查看 INNODB_TRX 事务表 发现  trx_state 的值分别为： tx1 RUNNING，tx2 和 tx3 LOCK WAIT;

     查看 INNODB_LOCKS 锁表 发现 lock_mode 的值分别为：tx1 -x ，tx2 与 tx3 都为 s；

以上说明，select 显示加 X 互斥锁的情况与 udpate 默认互斥锁是一样的，显示加锁后，其他加锁操作都需要等待释放数据集上的锁。也就是说，如果一个事务想对一个数据集加S 或 X 锁，而这个数据集正在另一个事务范围内，并被加X锁，则其他事务需等待，而默认select 是可以访问的，因为select没有锁的限制，但显示加锁的 select 语言需要wait lock，等待这个事务释放锁之后，才可以对数据集进行操作。

*** 扩展 ***

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。

InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 

****** 深入******

共享锁 S 与 读锁 X：

• 共享锁（S）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

       共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

• 排他锁（Ｘ）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

       排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的任何锁，包括共        享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

加锁 /如何加锁：

• mysql InnoDB引擎默认的修改数据语句，update,delete,insert 操作默认给涉及到的数据加上X 锁，select语句默认不会加任何锁类型
• 显示加锁： X 锁 >>> select ...for updat / S 锁 >>> select ... lock in share mode。
• 加 X锁的数据行,其他事务不能修改此数据，也无法通过 for update 和 lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

问题：

用select ... lock in share mode 获得共享锁 S ，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。

** 但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 select ...for updat  方式获得排他锁 X.

在MVCC（多版本并非控制）中，读操作可以分成两类：

     快照读 (snapshot read)与当前读 (current read)。

• 快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。

        简单的select操作，属于快照读，不加锁。

         select * from table where ?;

• 当前读，插入/更新/删除操作，属于当前读，需要加锁。

       下面语句都属于当前读，读取记录的最新版本。且读取后，还需要保证其他并发事务不能修改当前记录，对读取记录加         锁。以下除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

       select * from table where ? lock in share mode; // 显示加 S 锁
       select * from table where ? for update; // 显示加 X 锁
       insert into table values (…); // 自动加 X 锁
       update table set ? where ?; // 自动加 X 锁
       delete from table where ?; // 自动加 X 锁

事务隔离制度：

 数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏 感，可能更关心数据并发访问的能力。