一、事务简介

存储引擎如InnoDB、BDB才支持事务处理。

??每个事务(transaction)的处理必须满足ACID原则：

• 原子性(Atomicity):
  • 原子性指每个事务都必须被认为是一个不可分割的单元，即一个事务要么都执行，要么都不执行。
• 一致性(Consistency)
  • 一致性表示在事务开始之前和结束以后，数据库的完整性没有被破坏，保证了数据库从不返回一个未处理完的事务。
• 隔离性(Isolation)
  • 隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
• 持久性(Durability)
  • 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。在默认情况下，InnoDB表是100%持久的，MyISAM表在最后一个flush tables命令前进行的变化都会存盘。

二、MySQL事务

1. 事务控制

1.1 语法

• chain表示提交或回滚一个事务后立即启动一个新事务
• realease表示提交或回滚一个事务后断开与客户端的连接

1.2 例子

2. 事务隔离级别

2.1 隔离级别了解

??每一个事务都有一个隔离级，它定义了用户之间隔离和交互的程度。显然，在但用户环境中，它起不到独特的作用，但在多用户环境中，若不保证事务隔离，数据基本上会受到其他事务的影响，这将导致不一致性。

??基于ANSI/ISO SQL规范，MySQL定义了4种隔离级别，如下表所示，按照隔离级别逐渐降低排列。

2.2 设置隔离级别

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

• global表示定义的隔离级别适用于所有的用户
• session表示定义的隔离级别只适用于当前允许的会话和连接

2.3 查询当前隔离级别

-- 方法一：
select @@tx_isolation;

-- 方法二：
show variables like ‘tx_isolation‘;

三、并发简介

1. 并发导致的问题

??当多个用户并发地存取数据库的同一个数据时，若不加控制可能会导致存取和存储不正确的数据。

1.1 丢失更新问题(lost update)

??当两个或多个事务同时更新同一行数据时，最后的更新会覆盖其他事务所作的更新，这种问题就叫丢失更新问题。
??在完成并提交一个事务之前，其他事务不能访问统一文件或资源，这样可以避免这种问题的出现。

1.2 脏读问题(dirty read)

??一个事务正在对一条记录做修改，在这个事务完成并提交之前，这条记录的数据处于不一致的状态，若这是另一个事务也读取这条记录的数据，则这种现象就称为“脏读”。

1.3 不可重复读问题(unrepeatable read)

??当一个事务访问数据时，其他事务也访问该数据并对其进行修改，这会导致第一个事务两次读取到的数据不一样，这种情况就是不可重复读问题。

1.4 幻读问题(phantom read)

??当一个事务对某行数据进行插入或删除操作时，若其他事务也对这部分数据进行读取操作，则这个事务前后读取的内容可能会不同，这种情况就是幻读问题。

2. 锁

??当用户对数据库并发访问时，为了确保事务完整性和数据库的一致性，需要对其进行锁定，它是实现数据库并发控制的主要手段。锁是一种用来防止多个客户端同时访问数据时产生问题的机制。具体地说，锁可以防止出现上述并发导致的四种问题。

对于MySQL数据库而言，最显著的特点是不同的存储引擎支持的锁机制不同。

3. 死锁

??通常来说，死锁都是应用设计的问题，可通过调整业务流程、数据库对象设计、事务大小及访问数据库的SQL语句。

脚本之家：如何处理死锁

四、MySQL并发控制

1. MyISAM表的表级锁

• 对于读操作(select)，会自动给涉及的表加读锁。
  • 读锁不会阻塞其他进程对同一张表的读请求，但会阻塞对同一张表的写请求，需要等读锁释放后，其他进程才能进行写操作。
• 对于写操作(update、delete、insert)，会自动给涉及的表加写锁。
  • 写锁会阻塞其他进程对同一张表的读和写操作，需要等写锁释放后，其他进程才能进行读写操作。
• 用于锁是自动加的，所有不需要用户使用lock tables命令显式为表加锁。

1.1 查看表级锁争用情况

show status like ‘table_locks%‘;

1.2 锁模式

MySQL的表级锁有两种模式：表共享读锁(table read lock)、表独占写锁(table write lock)。

1.3 加锁方法

lock tables
tb_name [[as] new_tb_name] read [local] | [low_priority] write
...
unlock tables

• 当用户在一次查询时多次使用到一个被锁定的表，需要在锁定表的时候用as子句为表定义一个别名new_tb_name。
• read为读锁定，确保用户可以读表，但不能修改表。加上local表示允许表锁定后，用户可以进行非冲突的insert操作。
• write为写锁定，只有锁定该表的用户可以修改表，其他用户无法访问该表。加上low_priority表示允许其他用户读表。
• 表锁定只用于防止其他客户端进行不正当的读写操作。保持锁定的客户端依然可以进行表层级的操作，如drop table。
• 在事务表中，autocommit必须设置为0，否则在执行lock tables之后就会立即释放表锁定，很容易形成死锁。
• 在事务表中，锁定表时会隐式提交所有事务；在开始一个事务时会隐式解开所有表锁定。

2. InnoDB的行级锁

??InnoDB行锁是通过给索引上的索引项加锁来实现的，这意味着，只有通过索引条件检索诗句，InnoDB才使用行级锁，否则，InnoDB使用表级锁。与MySQL不同，Oracle是通过数据块中对相应数据行加锁来实现的。

• 对于读操作(select)，InnoDB不会加任何锁。
• 对于写操作(update、delete、insert)，会自动给涉及的数据集加排他锁(即写锁)。

2.1 查看行级锁争用情况

show status like ‘innoDB_row_lock%‘;

2.2 锁模式

InnoDB有两种类型的行锁：

• 共享锁(S, 即读锁)：允许一个事务去读一行，但会阻塞其他事务对相同数据集进行写操作。
• 排他锁(X, 即写锁)：允许获得排他锁的事务更新数据，但会阻塞其他事务对相同数据集进行读和写操作。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁(Intention Locks)，这两种意向锁为表锁。意向锁是InnoDB自动加的，不需要用户干预。

• 意向共享锁(IS)：事务打算给数据行加行共享锁(S)，在此之前必须先取得该表的IS锁。
• 意向排他锁(IX)：事务打算给数据行加行排他锁(X)，在此之前必须先取得该表的IX锁。

若一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务，否则该事务需要等待相应的锁释放。

2.3 加锁方法

-- 添加共享锁(S)
select * from tb_name where子句 ... lock in share mode;

-- 添加排他锁(X)
select * from tb_name where子句 ... for update;

• 若当前事务包含对数据的更新操作，应使用排他锁而不是共享锁，这样可以避免死锁。

NoteOfMySQL-13-事务与并发控制