事务是必须满足4个条件（ACID）

  • A (Atomicity) 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
  • C (Consistency) 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
  • I (Isolation)隔离性：一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰
  • D (Durability) 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚

并发事务处理带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

  • 更新丢失（ost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。
  • 脏读（Dirty Reads）：一个事务中访问到了另外一个事务未提交的数据。（一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。）
  • 不可重复读（Non-Repeatabe Reads）：一个事务读取同一条记录2次，得到的结果不一致。（一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。）
  • 幻读（Phantom Reads）：一个事务读取2次，得到的记录条数不一致。（一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。）

个人觉得多个select时，不用放入一个事务，select查询本身不需要事务提交。而如果在修改数据时，不提交事务，则会修改失败。

select只是用来进行查询操作，不需要事务回滚，因为select不会对数据库的产生持久化的修改，没有必要在数据发生不一致的时候进行回滚。如果要防止数据的不一致情况，可以通过修改事务的隔离级别实现，如设置隔离级别为RC或RR

事务的隔离级别

隔离级别 脏读（Dirty Read） 不可重复读（NonRepeatable Read） 幻读（Phantom Read）
未提交读（Read uncommitted） 可能 可能 可能
已提交读（Read committed） 不可能 可能 可能
可重复读（Repeatable read） 不可能 不可能 可能
可串行化（Serializable） 不可能 不可能 不可能
  • Oracle 默认级别是 RC『已提交读』；InnoDB 默认级别是：RR『可重复读』。【TIP: InnoDB 的 RR 级别利用 Next-key 锁解决了幻读问题，相当于 Serializable 级别了】
  • set global tx_isolation=‘read-committed’; // 设置数据库默认的事务隔离级别
  • 通过优化事务逻辑，大部分应用使用 Read Commited 隔离级别就足够了。
  • set autocommit=0; //设置不自动提交事务

查询当前数据库的事务隔离级别

  • mysql5.7: SHOW VARIABLES LIKE ‘tx_isolation’;
  • mysql8.0: show variables like ‘transaction_isolation’;

锁模式(InnoDB 有三种行锁的算法)

  1. 记录锁(Record Locks)

    单个行记录上的锁。对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；

    SELECT * FROM table WHERE id = 1 FOR UPDATE;

    它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行

    在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：

    1
2

    -- id 列为主键列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;

  2. 间隙锁（Gap Locks）

    间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。

    使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

    间隙锁只阻止其他事务插入到间隙中，他们不阻止其他事务在同一个间隙上获得间隙锁，所以 gap x lock 和 gap s lock 有相同的作用。

    对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻行。

    间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法，请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

    SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;

    即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。

    GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况

  3. 临键锁（Next-key Locks）

    临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。(临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。)

    Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

    对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

  4. 插入意向锁（Insert Intention）

    插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，亦即多个事务在相同的索引间隙插入时如果不是插入间隙中相同的位置就不需要互相等待。

    假设有索引值4、7，几个不同的事务准备插入5、6，每个锁都在获得插入行的独占锁之前用插入意向锁各自锁住了4、7之间的间隙，但是不阻塞对方因为插入行不冲突。

InnoDB共享锁和排他锁

InnoDB 行锁是通过给索引上的索引项加锁来实现的，不是针对记录加的锁。

在没有索引的情况下，InnoDB会对所有记录都加锁。

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁(X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。

共享锁作用：在你正在读的行设置一个共享锁，其他session也可以读这些行，但是直到你读完这些行，事务提交释放锁之后，其他sesstion才能更改这些行。如果你要读取的行正在被其他session修改，那么读取会卡住，直到其他session修改完毕，读取才能继续，并且读到的是最新版本的数据。

共享锁(S，读锁): select * from table_name where … lock in share mode;

排他锁作用：对于select的数据，会对这些行及其相关行加锁，效果和用update更新这些行时是一样的。其他对这些数据进行操作，无论是select、update也好，上面提到的SELECT … LOCK IN SHARE MODE也好，甚至在某些事务隔离级别下读取这些数据也好，所有这些操作都会被阻塞。

排他锁(X，写锁): select * from table_name where … for update;

select … lock in share mode的使用场景

引用mysql官网的例子，有两张表：parent表和child表，向child表插入数据时，要保证child的parent在parent表中存在，删除parent表时，会将parent以及该parent的child都删掉。一般的操作是先select，得到parentId，然后向child中插入一条数据(关联parentId)，但是这样是有问题的，如果select之后，插入之前，另一个session将parent删掉了，那么向child表中插入的数据并不会受到影响，最终造成该child没有parent的状况。

根据上面提到的特性，select时用select … lock in share mode就能解决这个问题。其实select … lock in share mode 在平时用到的场景很少很少，用的比较多的还是select … for update。

select … for update的使用场景

账户表中有一个字段money，取出来后，将money更新(比如加上一个值)后再存进去。对于这个场景，如果两个session同时select，比如都取出来的是100，然后都加30, 都变成130，然后都update money，最终money的值是130，与预期的160不符。这个时候用select … for update 就能完美解决这个问题，这时因为两个session不能同时select … for update。

思考

上述select … for update的场景如果使用select … lock in share mode会怎样？

答案是，会很大概率造成死锁，造成死锁的原因是：session A和session B同时select lock in share mode, 这时都未提交事务，session A 继续执行update操作，此时因为session B事务还没提交，锁还没释放，所以session A的update操作会被阻塞，等待session B释放锁，同样的，session B此时也在等待session A提交事务释放锁，这就发生了死锁。

所以 lock in share mode 使用时要仔细检查，确认该场景是否真的适用且不会发生死锁。

MyISAM表锁

1
2

lock table table_name read;  //读锁
lock table table_name write;  //写锁

死锁

解决死锁之路 - 常见 SQL 语句的加锁分析

死锁产生：

  • 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
  • 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁
  • 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。
  • 两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。
  • 发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放并回退，另一个事务获得锁，继续完成事务。
  • 设置锁等待超时参数 set global innodb_lock_wait_timeout=120, 这样可以避免高并发访问情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库。
  • 通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及范根数据库的SQL语句，绝大部分死锁都是可以避免。

同一事务中，mysql在insert后，可以直接select到刚插入的数据。

如果出现死锁，可以用 show engine innodb status;命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。