mysql中的事务隔离性

事务拥有原子性、隔离性、一致性、持久性（acid）。

• 原子性：要么全成功，要么全失败。
• 隔离性：事务之间必须保持隔离性，互补干扰，不然会出现脏读，不可重复读和幻读。
• 一致性：事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。如果事务成功地完成，那么系统中所有变化将正确地应用，系统处于有效状态。如果在事务中出现错误，那么系统中的所有变化将自动地回滚，系统返回到原始状态。分布式中难点就是事务的一致性。
• 持久性：事务提交的结果，将永远保持在数据库中。

注意：mysql中的innodb引擎支持事务，myisam不支持。

mysql中的事务自动提交AUTOCOMMIT

在Innodb存储引擎中事务默认采取自动提交的模式，也就是说，如果不是显式的开始一个事务，则每个sql都当作是一个事务执行操作，同样遵循mvcc，直到显式的执行commit或者rollback表示该事务结束。
在当前连接中可以设置autocommit变量来启用或者关闭自动提交，0为关闭，1为开启。

 mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 行于数据集 (0.04 秒)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 秒)

测试：客户端A和客户端B设置autocommit为关闭状态，对行加排他锁
1.在客户端A加锁

mysql> update users set name='ds' where id=12;
Query OK, 1 rows affected (0.02 秒)

2. 在客户端B上也update

mysql> update users set name='ds' where id=12;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

按照之前的理解，在客户端B上肯定是不会加锁的，且是可以执行成功的。事实证明显id为12的数据已经加来排他锁。

3. 在客户端A上执行commit后，客户端B上id为12的行获得排他锁，执行成功

mysql> commit;
 Query OK, 0 rows affected (0.01 秒)

注意：修改autocommit变量对非事务类型的表，比如myisam或者内存表，不会有任何的影响，对这类表没有commit或者rollback的概念，也可以说一只处于autocommit启用的模式

误区：之前以为只有start transaction来算是事务执行，其实每个sql都是一个事务的提交，只不过在innodb中是采用的自动提交的方式。

并发造成事务隔离性破坏

• 第一类丢失更新
  在没有事务隔离的情况下，俩个事务都同时更新一条数据，但是第二个事务却中途退出，导致对第一个事务的更新失败。
  例：张三读取火车票，查询剩余500张，同时李四也查询并且购买了一张，剩余499张，并且修改了数据库。随后，张三购买过程中发生异常，导致事务回滚，票数恢复为500，这样就导致了李四的修改失败。

• 第二类丢失更新，不可重复读的特例
  俩个并发事务同时读取一行数据，然后其中一个对其进行了修改，另一个也进行了修改，然后造成了第一个写操作的失败。
  例：现在火车票还剩500张， 张三读取到票还有500张，操作还未完成，事务还未提交。于此同时，李四购买了一张票，剩余票为499.最后提交了事务。随后，张三也购买了一张，剩余票又为499，但是应该是498。覆盖了张三的事务。

• 脏读
  是指事务a修改了数据库的某行数据，但并未提交事务时，事务b这时要取这行数据进行相关操作，读到的数据是a已经修改过的了，接着a事务由于某些原因回退。导致b事务读到的这行数据和数据库不一致，这就是脏读。
  例：火车票剩余500张，张三购买了一张火车票，数据库修改但是事务没提交
  随后李四也要购买，查到剩余火车票为499张，接着张三发生突发事情，事务回退，火车票剩余500张，这时张三读到的数据和数据库不一致，导致脏读。

• 不可重复读
  不可重复读是指在对于数据库中的某条数据，一个事务范围内多次查询返回不同的数据值(这里不同是指某一条或多条数据的内容前后不一致，但数据条数相同)，这是由于在查询间隔，该事务需要用到的数据被另一个事务修改并提交了。需要注意的是在某些情况下不可重复读并不是问题。不可重复读可能发生在update,delete操作中

  例：现在火车票有500张, 张三购买火车票，读到还有500张，操作还没有完成，事务还没完成。于此同时李四也要买车票，并且购买了一张，并提交了事务。这是张三又查了一遍火车票，变成了499张，在一个事务中前后读取到的俩次数据不一致，导致了不可重复读。

• 幻读
  幻读我根据事务隔离级别不同总结为一下俩种，在repeatable read级别中实现的写幻读，没实现读幻读。在serializable级别中实现了写幻读和读幻读，实现原理请我的另一篇文章mysql的锁机制

  • 写幻读：比如事务T1 update(或者delete，当前读都行)数据库中某个字段1改成2，比如此字段非唯一索引。这时这些数据会加上x锁，同时这些数据之间也会加上GAP间隙锁。加锁情况如下图。GAP锁的作用就是禁止插入数据。只要T1事务不提交，x锁和GAP锁就一直存在。而此时T2数据想插入一条这个字段为1的数据，这时会提示处于加锁状态，T2事务必须等T1事务结束后才可插入。这样也就避免了T1事务update完字段1改为2后，又出现了该字段为1的数据。
  • 读幻读：比如事务T1查询主键id小于20的数据行为10行数据，同时事务T2插入了一条id为3的数据，并且T2事务提交，此时事务T1再次查询id小于20的数据时为11条，有事务T2的数据，此时造成幻读。为什么T2事务还能插入数据呢？因为T1事务在查询id小于20的数据时并没有给这些数据和数据行间加间隙锁，所以可以插入新数据。而serializable隔离级别就是在快照读查询时也会加锁和间隙锁。幻读常发生在insert操作中。

  例：目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。此时，事务B插入一条工资也为5000的记录。这是，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。

事务的隔离级别
mysql的innodb引擎的相应的隔离级别可以解决相应的上述问题。innodb默认的隔离级别是repeatable_read,这个级别可以解决第一类更新失败、第二类更新失败、脏读、不可重复读的问题，没解决幻读问题

• read-uncommited(读取未提交的)
  在该隔离级别，所有事务都可以看到其他事务未提交的执行结果。这是事务最低的隔离级别。解决第一类丢失更新和第二类丢失的问题，但是会出现脏读、不可重复读、幻读 。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少

• read-commited(读取已提交的)
  保证一个事务修改的数据提交后才能被另外一个事务读取，即另外一个事务不能读取其他事务未提交的数据。解决第一类丢失更新、第二类丢失更新和脏读的问题，但会出现不可重复读和幻读问题

• repeatable-read(可重复读)
  保证一个事务相同条件下前后两次获取的数据是一致的。解决第一类丢失更新，第二类丢失更新，脏读、不可重复读的问题，但会出幻读的问题

• serializable(串行化)
  在这个级别，可能导致大量的超时现象和锁竞争。只要操作产生了共享锁，就不允许其他事务修改！简而言之也是解决幻读的关键--也就是事务会将读取的数据集（数据行）加上共享锁，其他事务不能写，可以读，直到事务结束，共享锁释放。它解决了所有隔离性相关问题，但是生产环境不会使用该隔离级别，效率太低。

注意：隔离级别越高，出现的并发问题就越少，但是这样消耗的性能更大，而且并发能力会很低。所以适合自己的隔离级别是最重要的。

由于事务第一类更新丢失和第二类更新丢失不会发生，这里不做讨论。

隔离级别

事务相关命令

• 查看当前的事务隔离级别

  select @@global.tx_isolation; //查看全局的隔离级别，生产环境使用
  select @@session.tx_isolation;//查看当前会话级别，下面测试使用它查询
  select @@tx_isolation;
  

• 设置事务隔离级别

  set [global | session ] transaction isolation level [read uncommitted | read committed | repeatable read | serializable]
  

  1.默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别，得重新开启事务，不能使用本次事务。
  2.如果你使用GLOBAL关键字，是在修改完之后创建的所有新连接（除了不存在的连接）设置默认事务级别，你需要SUPER权限来做这个，生产环境使用global。
  3.使用SESSION 关键字将在当前连接上执行的事务设置默认事务级别，不需要重新开启事务。 任何客户端都能自由改变会话隔离级别（甚至在事务的中间），或者为下一个事务设置隔离级别。

• mysql命令

  start transaction;//开始事务
  ......
  sql语句
  ......
  commit;//提交事务
  rollback;//事务回滚
  

测试
在A客户和B客户端同时修改事务隔离级别。使用以下命令来设置隔离级别,使用session。特别注意：更改隔离级别之后测试的事务不能使用之前的事务，之前的事务应该commit或者rollback。在更改完隔离级别之后重新start事务测试。

set session transaction isolation level [read uncommitted | read committed | repeatable read | serializable]

---

• 客户端A和B隔离级别为read uncommitted
  1.客户端A开启事务:

  mysql> start transaction;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> select * from y_login;
  +----+---------+---------+-----------+
  | id | phone   | islogin | logintime |
  +----+---------+---------+-----------+
  | 20 | 896     |       2 | NULL      |
  | 21 | 5623656 |    NULL | NULL      |
  | 22 | 666666  |    NULL | NULL      |
  +----+---------+---------+-----------+
  3 rows in set (0.00 sec)
  

  2. 客户端b也同时开启事务：

  mysql> start transaction;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> select * from y_login;
  +----+---------+---------+-----------+
  | id | phone   | islogin | logintime |
  +----+---------+---------+-----------+
  | 20 | 896     |       2 | NULL      |
  | 21 | 5623656 |    NULL | NULL      |
  | 22 | 666666  |    NULL | NULL      |
  +----+---------+---------+-----------+
  3 rows in set (0.00 sec)
  

  3.客户端A上修改id为22的数据

  mysql> update y_login set phone=888 where id=22;      
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  mysql> select * from y_login;
  +----+---------+---------+-----------+
  | id | phone   | islogin | logintime |
  +----+---------+---------+-----------+
  | 20 | 896     |       2 | NULL      |
  | 21 | 5623656 |    NULL | NULL      |
  | 22 | 888     |    NULL | NULL      |
  +----+---------+---------+-----------+
  

  4. 在客户端b上查询id为22的数据

      mysql> select * from y_login;
      +----+---------+---------+-----------+
      | id | phone   | islogin | logintime |
      +----+---------+---------+-----------+
      | 20 | 896     |       2 | NULL      |
      | 21 | 5623656 |    NULL | NULL      |
      | 22 | 888     |    NULL | NULL      |
      +----+---------+---------+-----------+
     

  可以看到读到了客户端A未提交的事务，这时就肯定造成了脏读。用户可以拿到这个未提交的phone数据做一些其他的相关操作，最后客户端A如果rollback的话，关于phone的一系列操作都是错误的。

  5.在客户端B上对id为22的数据进行写操作

  mysql> update y_login set phone=999 where id=22;      
  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  

  可以看到由于客户端A对这行数据进行了写操作，所以这行数据加锁了，这行数据可以读，但是不能写，所以事务中进行写操作的行会加排他锁（innodb中是行锁，排他锁简称写锁）,就避免了第二类更新丢失的问题。将客户端A的事务commit或者rollback后，客户端A释放排他锁，客户端B抢到排他锁后可进行读写操作,客户端A释放时间为6.94s。

  mysql> update y_login set phone=999 where id=22;
  Query OK, 1 row affected (6.94 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  

  在客户端 C上，没有开启事务正常执行id为22的写操作时，同样被加锁，只有客户端A释放排他锁时，才能执行id为22的写操作。

---

• 客户端A和B隔离级别为read committed
  1.客户端A开启事务:

  mysql> start transaction;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> select * from y_login;
  +----+---------+---------+-----------+
  | id | phone   | islogin | logintime |
  +----+---------+---------+-----------+
  | 20 | 896     |       2 | NULL      |
  | 21 | 5623656 |    NULL | NULL      |
  | 22 | 8823    |    NULL | NULL      |
  +----+---------+---------+-----------+
  3 rows in set (0.00 sec)
  

  2. 客户端b也同时开启事务：

  mysql> start transaction;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> select * from y_login;
  +----+---------+---------+-----------+
  | id | phone   | islogin | logintime |
  +----+---------+---------+-----------+
  | 20 | 896     |       2 | NULL      |
  | 21 | 5623656 |    NULL | NULL      |
  | 22 | 8823    |    NULL | NULL      |
  +----+---------+---------+-----------+
  3 rows in set (0.00 sec)
  

  3.客户端A上修改id为22的数据

  mysql> update y_login set phone=888 where id=22;      
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  mysql> select * from y_login;
  +----+---------+---------+-----------+
  | id | phone   | islogin | logintime |
  +----+---------+---------+-----------+
  | 20 | 896     |       2 | NULL      |
  | 21 | 5623656 |    NULL | NULL      |
  | 22 | 888     |    NULL | NULL      |
  +----+---------+---------+-----------+
  

  4. 在客户端B上查看数据，id为22的phone依然是88823，不会读取未提交的事务的数据。

      mysql> select * from y_login;
      +----+---------+---------+-----------+
      | id | phone   | islogin | logintime |
      +----+---------+---------+-----------+
      | 20 | 896     |       2 | NULL      |
      | 21 | 5623656 |    NULL | NULL      |
      | 22 | 88823   |    NULL | NULL      |
      +----+---------+---------+-----------+
     

  5. 在做个测试，在客户端B上修改id为22的数据

      mysql> update y_login set phone=888 where id=22;
      ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
     

     可以看到由于客户端A对这行数据进行了写操作，所以这行数据加锁了，这行数据可以读，但是不能写，所以事务中进行写操作的行会加排他锁,就避免了第二类更新丢失的问题。将客户端A的事务commit或者rollback后，客户端A释放排他锁，客户端B抢到排他锁后可进行读写操作,客户端A释放时间为18s。

      mysql> update y_login set phone=999 where id=22;
      Query OK, 1 row affected (18 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
     

  在客户端 C上，没有开启事务正常执行id为22的写操作时，同样被加锁，只有客户端A释放排他锁时，才能执行id为22的写操作。

  6. 当客户端A提交事务，后查看客户端B的数据

     mysql> select * from y_login;
     +----+---------+---------+-----------+
     | id | phone   | islogin | logintime |
     +----+---------+---------+-----------+
     | 20 | 896     |       2 | NULL      |
     | 21 | 5623656 |    NULL | NULL      |
     | 22 | 888     |    NULL | NULL      |
     +----+---------+---------+-----------+
     

  可以看到id为22的数据已经更新，读取的是事务提交后的数据。这样就避免了脏读，但是出现了不可重复读的问题。

---

• 客户端A和B隔离级别设置为 repeatable read
  1.客户端A开启事务:

  mysql> start transaction;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> select * from y_login;
  +----+---------+---------+-----------+
  | id | phone   | islogin | logintime |
  +----+---------+---------+-----------+
  | 20 | 896     |       2 | NULL      |
  | 21 | 5623656 |    NULL | NULL      |
  | 22 | 888     |    NULL | NULL      |
  +----+---------+---------+-----------+
  3 rows in set (0.00 sec)
  

  2. 客户端b也同时开启事务：

  mysql> start transaction;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> select * from y_login;
  +----+---------+---------+-----------+
  | id | phone   | islogin | logintime |
  +----+---------+---------+-----------+
  | 20 | 896     |       2 | NULL      |
  | 21 | 5623656 |    NULL | NULL      |
  | 22 | 888     |    NULL | NULL      |
  +----+---------+---------+-----------+
  3 rows in set (0.00 sec)
  

  3.客户端A上修改id为22的数据

  mysql> update y_login set phone=333 where id=22;      
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  mysql> select * from y_login;
  +----+---------+---------+-----------+
  | id | phone   | islogin | logintime |
  +----+---------+---------+-----------+
  | 20 | 896     |       2 | NULL      |
  | 21 | 5623656 |    NULL | NULL      |
  | 22 | 333     |    NULL | NULL      |
  +----+---------+---------+-----------+
  

  4. 在客户端B上查看数据，id为22的phone依然是888，不会读取未提交的事务的数据。

      mysql> select * from y_login;
      +----+---------+---------+-----------+
      | id | phone   | islogin | logintime |
      +----+---------+---------+-----------+
      | 20 | 896     |       2 | NULL      |
      | 21 | 5623656 |    NULL | NULL      |
      | 22 | 888     |    NULL | NULL      |
      +----+---------+---------+-----------+
     

  5. 在做个测试，在客户端B上修改id为22的数据

      mysql> update y_login set phone=545 where id=22;
      ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
     

  可以看到由于客户端A对这行数据进行了写操作，所以这行数据加锁了，这行数据可以读，但是不能写，所以事务中进行写操作的行会加排他锁,就避免了第二类更新丢失的问题。将客户端A的事务commit或者rollback后，客户端A释放排他锁，客户端B抢到排他锁后可进行读写操作,客户端A释放时间为18s。

      mysql> update y_login set phone=545 where id=22;
      Query OK, 1 row affected (18 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
  

  在客户端 C上，没有开启事务正常执行id为22的写操作时，同样被加锁，只有客户端A释放排他锁时，才能执行id为22的写操作。

  6. 当客户端A提交事务，后查看客户端B的数据

      mysql> select * from y_login;
      +----+---------+---------+-----------+
      | id | phone   | islogin | logintime |
      +----+---------+---------+-----------+
      | 20 | 896     |       2 | NULL      |
      | 21 | 5623656 |    NULL | NULL      |
      | 22 | 888     |    NULL | NULL      |
      +----+---------+---------+-----------+
     

  可以看到id为22的依然是888，并没有因为客户端A的提交而改变数据。所以不可重复读问题解决。

  7. 客户端B提交事务，查看客户端B的数据

        mysql> select * from y_login;
        +----+---------+---------+-----------+
        | id | phone   | islogin | logintime |
        +----+---------+---------+-----------+
        | 20 | 896     |       2 | NULL      |
        | 21 | 5623656 |    NULL | NULL      |
        | 22 | 333     |    NULL | NULL      |
        +----+---------+---------+-----------+
     

     8. 上面实现了repeatable read级别的可重复读，接下来实验repeatable read级别可解决写幻读，而不能解决读幻读，俩者区别请看mysql的锁机制。前提是account为非唯一索引，id为主键索引也就是聚簇索引，索引类型为b+tree具有有序性。

     user数据库数据为下

     
     

     • 首先在客户端A开启事务，然后执行sql。加锁过程为先在二级索引account上将account=3的数据加上x锁，然后在account=3的数据间加上间隙锁。最后在主键索引上对相应的数据加 x锁。具体原理请读mysql的锁机制

         mysql> start transaction;
         Query OK, 0 rows affected (0.02 秒)
       
         mysql> update users set name=111 where account=3;
         Query OK, 0 rows affected (0.01 秒)
       

     • 由于在二级索引中的account=3的数据间加上了间隙锁，所以在并发事务中插入account=3的数据应该提交被加锁,插入account不为3的数据应该不被加锁。在客户端b上实验,证明猜想正确。

       mysql> start transaction;
       Query OK, 0 rows affected (0.02 秒)
       
       mysql> insert into users(id,name,account) values(45,'11',3);
       Lock wait timeout exceeded; try restarting transaction
       
       mysql> insert into users(id,name,account) values(453,'11',4);
       Query OK, 1 rows affected (0.01 秒)
       

     • 由上面数据可以看出，在二级索引加上x锁和GAP锁后，在聚簇索引id为11和20的数据上也会被加了x锁。id不是11或20不会被加上x锁。在客户端b上实验。

        mysql> update users set name=111 where id=12;
        Query OK, 0 rows affected (0.01 秒)
       
        mysql> update users set name=111 where id=11;
        Lock wait timeout exceeded; try restarting transaction
       

     9. 继续测试repeatable read级别下的写幻读。前提是name不是索引，id为主键索引也就是聚簇索引，索引类型为b+tree具有有序性。

        数据库内容同上

        
        

     • 首先在客户端A开启事务，然后执行sql。由于没有使用索引，所以会用聚簇索引进行全表扫描，随后会在聚簇索引上的所有记录都被加上了X锁。其次，聚簇索引每条记录间的间隙也同时被加上了GAP锁，也就是说这个事务没提交之前，在并发事务中不能插入任何的数据，避免了写幻读。也不能修改和删除任何数据，因为所有的数据都加上了x锁。这是个很恐怖的事情,这是就会锁表，也无法修改表结构。

       mysql> start transaction;
       Query OK, 0 rows affected (0.02 秒)
       
       mysql> update users set account=111 where name='22424';
       Query OK, 1 rows affected (0.03 秒)
       

     • 接着在客户端b随便插入一条新纪录，提示被加锁，防止了写幻读的出现。由于所有记录被加x锁，所以修改和删除也被提示被加锁。

       mysql> start transaction;
       Query OK, 0 rows affected (0.01 秒)
       
       mysql> insert into users(id,name,account) values(43,'11',3);
       Lock wait timeout exceeded; try restarting transaction
       
       mysql> update users set name=111 where id=11;
       Lock wait timeout exceeded; try restarting transaction
       
       mysql> delete from users where id=14;
       Lock wait timeout exceeded; try restarting transaction
       

---

• 客户端A和B设置隔离级别为serializable
  事务会将获取的数据集加上一个共享锁，且每条数据间都会加上间隙锁，相关数据集的写操作都不能进行，直到共享锁释放也就是事务提交。

  1. 在客户端A开始事务并查询

      mysql> start transaction;
      Query OK, 0 rows affected (0.00 sec)
     
      mysql> select * from y_login;
      +----+---------+---------+-----------+
      | id | phone   | islogin | logintime |
      +----+---------+---------+-----------+
      | 21 | 5623656 |    NULL | NULL      |
      | 23 | 54115   |    NULL | NULL      |
      | 24 | 541115  |    NULL | NULL      |
      +----+---------+---------+-----------+
     

  这时就在id为21到24的数据集加上了共享锁和间隙锁,更准确的说是把这个表里面的所有数据行加了共享锁和间隙锁，因为并没有查询条件，之前插入或删除修改该表的数据都会提示加锁，不能操作。

2. 在客户端B上,更新id为24数据,由于共享锁不能加派台锁，所以报加锁问题

    mysql> update y_login set phone=23 where id=24;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
   

3. 在客户端上，新增一条记录，由于每条记录间加了间隙锁，所以报加锁问题。

    mysql> insert into y_login(phone) values(2442);
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
   

现在解决了写幻读和读幻读的的问题，但是效率很差，很多超时的现象出现。repeatable read隔离级别只能解决写幻读的问题。

4. 在做个测试，在客户端A查询id小于24的数据集

    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
   
    mysql> select * from y_login where id<24;
    +----+---------+---------+-----------+
    | id | phone   | islogin | logintime |
    +----+---------+---------+-----------+
    | 21 | 5623656 |    NULL | NULL      |
    | 23 | 54115   |    NULL | NULL      |
    +----+---------+---------+-----------+
   

这里是将id小于24的数据集都加共享锁，猜想如果插入一条数据id小于24会报加锁问题，如果id大于24的话应该可以插入。在客户端B插入数据

    insert into y_login(id,phone) values(25,2442);
    Query OK, 1 row affected (0.01 sec)
    insert into y_login(id,phone) values(1,2442);
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

证明猜想正确。

思考

前提是innodb存储引擎下，在myisam存储引擎下是不支持事务的。在各个隔离级别下，事务如果没提交，但是sql已经执行完后，会直接修改写入数据库文件吗？还是必须在事务结束后才会写到数据库文件呢？

之前认为是事务开始后，执行了sql语句后，数据库就会立马改变，最后数据库在根据事务的结果进行rollback还是commit。一个不经意的测试，结果并不是这样，数据库而是在事务结束后根据事务的状态来判断是否更新状态。

• 事务隔离级别 read uncommitted

  1. 在客户端A开始事务，此时数据库中id为12的password为42222

      mysql> start transaction;
      Query OK, 0 rows affected (0.01 秒)
     
      mysql> select password from users where id=12;
      +----------+
      | password |
      +----------+
      | 42222    |
      +----------+
     

  2. 继续在客户端操作，更改id为12的password

      mysql> update users set password=333 where id=12;
      Query OK, 1 rows affected (0.01 秒)
     

  3. 在客户端B上查询，已经变为33

      mysql> select password from users where id=12;
      +----------+
      | password |
      +----------+
      | 333      |
      +----------+
     

  4. 查看数据库，此时数据未改变333，仍然未事务开始时数据42222

     
     WX20190620-175004@2x.png

  5. 在客户端A上事务commit后，数据改变为333。

  测试剩余的隔离级别也是同样情况，磁盘中的mysql文件只有在事务提交后才会相应的改变,也就是在事务结束后才会io操作。至于sql为什么查询出来的改变了？
  引用《高性能mysql》一说中所说，是因为事务日志，事务日志可以提高事务的效率，使用事务日志，存储引擎在修改表数据的时候只需要修改并往内存拷贝，事务提交后在将内存的行记录记录到硬盘中的文件中，而不用每次将修改的数据本身持久化到磁盘中。事务日志持久之后，内存中修改的数据可以慢慢刷新到磁盘中。如果数据的修改已经记录到事务日志并持久化，但数据还没有同步到磁盘，此时系统崩溃的话，存储引擎会在重启后自动回复这部分修改的内容。

  由此知道了，事务的执行顺序是start transaction，随后写入事务日志，如果rollback的话根据事务日志中的事务id进行回退，如果commit的话将事务日志中的内容写入到磁盘中进行持久化。

总结

在隔离性为read uncommitted,read committed,repeatable read和serializable的事务中会对写操作的行加共享锁。在隔离性为serializable的事务中会对读到的数据集加共享锁也会对写操作的行加共享锁。