MySQL数据库并发与事务相关问题

MySQL的并发控制

读写锁

• 共享锁（shared lock）：即读锁（read lock），读锁是共享的，也就是说是相互不阻塞的，多个客户在同一时刻可以同时读取同一个资源，而互不干扰；
• 排他锁（exclusive lock）：即写锁（write lock），写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁。

锁粒度

• 表锁（table lock）：最基本的锁策略，且开销最小，表锁会锁定整张表。在对表进行写操作（增删改）前，需要先获得写锁，写锁会阻塞其他用户对该表的所有读写操作；只有没有写锁时，其他读取的用户才能获得读锁，读锁间不互相阻塞。
• 行级锁（row lock）：行级锁可以最大程度支持并发处理，但是会带来最大的锁开销。

大多数商业数据库系统一般都是在表上施加行级锁。

事务

事务就是一组原子性的SQL查询，事务内的语句，要么全部执行成功，要么全部执行失败。

命令

• START TRANSACTION：开始一个事务；
• COMMIT：提交事务将修改的数据持久保留；
• ROLLBACK：撤销所有更改。

特性ACID

• 原子性（atomicity）：整个事务中的所有操作要么全部提交成功，要么全部失败回滚；
• 一致性（consistency）：数据库总是从一个一致性状态转换到另一个一致性状态；
• 隔离性（isolation）：通常一个事务所做的修改在最终提交前，对其他事务不可见；
• 持久性（durability）：一旦事务提交，则其所做的修改会永久保存到数据库中。

并发问题

• 脏读（Dirty Read）：事务可以读取未提交的数据。事务1第二次读取时，读到了事务2未提交的数据。若事务2回滚，则事务1第二次读取时，读到了脏数据。

  

• 不可重复读（Nonrepeatable Read）：两次执行相同的查询，可能会得到不同的结果。主要在于事务2在事务1第二次读取时，提交了数据。导致事务1前后两次读取的数据不一致。不可重复读针对的是值的不同。

  

• 幻读（Phantom Read）：当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会发生幻行（Phantom Row）。事务1第二次查询时，读到了事务2提交的数据。幻读针对的是数据条数的不同。

  

隔离级别

• READ UNCOMMITTED（未提交读）：事务中的修改，即使没有提交，对其他事务也是可见的。也就是说事务可以读取未提交的数据（脏读Dirty Read），实际中很少使用；
• READ COMMITTED（提交读）：即NONREPEATABLE READ（不可重复读），Oracle数据库的默认隔离级别，也是大多数数据的默认隔离级别（MySQL不是）。一个事务开始时，只能看见已经提交的事务所做的修改，即一个事务从开始到提交之前，所做的任何修改对其他事务都是不可见的。
• REPEATABLE READ（可重复读）：MySQL数据库的默认隔离级别，解决了脏读和不可重复读的问题，该级别保证了在同一个事务中多次读取同样记录的结果是一致的。
• SERIALIZABLE（可串行化）：最高的隔离级别，通过强制事务串行执行，避免了幻读问题，该级别在读取的每一行数据上都加锁，会导致大量的超时和锁争用问题。

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	√	√	√
READ COMMITTED	×	√	√
REPEATABLE READ	×	×	√
SERIALIZABLE	×	×	×

死锁

两个或多个事务在同一个资源上相互占用，请求锁定对方占用的资源，从而导致恶行循环的现象。InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。

多版本并发控制

MVCC（Multi-Version Concurrency Control）是行级锁的一种变种，但它在很多情况下避免了加锁操作，因此开销更低，不同存储引擎对MVCC的实现有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。MVCC 是现代数据库（包括 MySQL 、 Oracle 、 PostgreSQL 等）引擎实现中常用的处理读写冲突的手段， 目的在于提高数据库高并发场景下的吞吐性能 。

如此一来不同的事务在并发过程中， SELECT 操作可以不加锁而是通过 MVCC 机制读取指定的版本历史记录，并通过一些手段保证保证读取的记录值符合事务所处的隔离级别，从而解决并发场景下的读写冲突。

InnoDB中的MVCC

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列实现的，一个保存了行的创建时间DATA_TRX_ID，一个保存了行的过期时间（删除时间）DATA_ROLL_PTR，如果插入的行没有设定主键，则会多一个隐藏的主键列DB_ROW_ID：

• DATA_TRX_ID：记录最近更新这条行记录的事务 ID ，大小为6个字节

• DATA_ROLL_PTR：表示指向该行回滚段的指针，大小为7个字节， InnoDB 便是通过这个指针找到之前版本的数据。该行记录上所有旧版本，在 undo 中都通过链表的形式组织。

• DB_ROW_ID：行标识（隐藏单调自增 ID ），大小为6字节，如果表没有主键， InnoDB 会自动生成一个隐藏主键，因此会出现这个列。另外，每条记录的头信息里都有一个专门的 bit （ deleted_flag ）来表示当前记录是否已经被删除。

保存这两个额外的系统版本号，使大多数读操作都不用加锁。MVCC只在RR和RC两个隔离级别下工作，其他两个隔离级别用不到MVCC，因为RU总是读取最新的数据行，SERIALIZABLE则会对所有读取的行都加锁。

组织版本链

在多个事务并行操作某行数据的情况下，不同事务对该行数据的UPDATE会产生多个版本，然后通过回滚指针组织成一条 Undo Log 链，下图是一个由ID为10的事务创建的一行sher数据，然后由ID为20的事务将该行数据更新为sher6，然后ID为30的事务将该行数据更新为sher6j。这样的话，就可以根据DATA_ROLL_PTR指针查看以前的版本，并可以进行回滚。