MySQL数据库（四）- 事务和锁

目录

事务的特性

隔离级别

锁

      行锁、页锁和表锁

      共享锁和排它锁

      意向锁（Intent Lock）

      乐观锁和悲观锁

      避免死锁的发生

MVCC

      快照读

      当前读

      MVCC原理

---

事务的特性

        ACID

        A，原子性（Atomicity）。操作不可分割，中间状态对外不可见。

        C，一致性（Consistency）。一致性指的就是数据库在进行事务操作后，会由原来的一致状

态，变成另一种一致的状态。也就是说当事务提交后，或者当事务发生回滚后，数据库的完整性约

束不能被破坏。

        I，隔离性（Isolation）。每个事务都是彼此独立的，不会受到其他事务的执行影响。一个事

务在提交之前，对其他事务都是不可见的。

        D，持久性（Durability）。事务提交之后对数据的修改是持久性的。当事务完成，数据库的日

志就会被更新，这时可以通过日志，让系统恢复到最后一次成功的更新状态。

        原子性是基础，隔离性是手段，一致性是约束条件，而持久性是我们的目的。

        通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对

数据库中对应的行进行修改。

        

        事务相关的命令：

        SHOW ENGINES 命令来查看当前 MySQL 支持的存储引擎都有哪些，以及这些存储引擎是

否支持事务。

        START TRANSACTION 或者 BEGIN，作用是显式开启一个事务。

        COMMIT：提交事务。当提交事务后，对数据库的修改是永久性的。

        ROLLBACK 或者 ROLLBACK TO [SAVEPOINT]，意为回滚事务。

        SAVEPOINT：在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多

个保存点。

        RELEASE SAVEPOINT：删除某个保存点。

        SET TRANSACTION，设置事务的隔离级别。

        

        MySQL 默认自动提交

        mysql> set autocommit =0; //关闭自动提交

        mysql> set autocommit =1; //开启自动提交

        SET @@completion_type = 1;

        completion=0（默认配置）

        COMMIT提交事务之后，在执行下一个事务时，需要使用 START TRANSACTION 或者

BEGIN 来开启。

        completion=1

        COMMIT提交之后，相当于在下一行写了一个 START TRANSACTION 或 BEGIN。

completion=2，COMMIT=COMMIT AND RELEASE，当时会务提交后，会自动与服务器断开连

接。

        

隔离级别

        事务并发处理可能存在的三种异常情况：

        脏读：读到了其他事务还没有提交的数据。

        不可重复读：对某数据进行读取，发现两次读取的结果不同。这是因为有其他事务对这个数

据同时进行了修改或删除。 - 并发修改数据；

        幻读：事务A根据条件查询得到了N条数据，但此时事务B更改或者增加了M条符合事务A查询

条件的数据，这样当事务A再次进行查询的时候发现会有 N+M 条数据，产生了幻读。- 并发新增数

据；

        

        查看隔离级别

        mysql> SHOW VARIABLES LIKE 'transaction_isolation';

        修改隔离级别

        mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

 

        设置不同的隔离等级，以便在正确性和效率之间进行平衡。

        隔离级别越低，意味着系统吞吐量（并发程度）越大，但同时也意味着出现异常问题的可能

性会更大。在实际使用过程中我们往往需要在性能和正确性上进行权衡和取舍，没有完美的解决方

案，只有适合与否。

锁

      行锁、页锁和表锁

        行锁就是按照行的粒度对数据进行锁定。

        页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。

        表锁就是对数据表进行锁定，锁定粒度很大，同时发生锁冲突的概率也会较高，数据访问的

并发度低。

        锁定力度小，发生锁冲突概率低，可以实现的并发度高，但是对于锁的开销比较大，加锁会

比较慢，容易出现死锁情况。

        每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层

级的锁数量超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小

粒度的锁。

      共享锁和排它锁

        共享锁也叫读锁或S锁，共享锁锁定的资源可以被其他用户读取，但不能修改。当对数据表加

上共享锁的时候，该数据表就变成了只读模式。

        给表加共享锁

        LOCK TABLE table1 READ;

        UNLOCK TABLE;

        给数据行加共享锁

        SELECT * FROM table1 WHERE id = 123 LOCK IN SHARE MODE

        

        排它锁也叫独占锁、写锁或X锁。排它锁锁定的数据只允许进行锁定操作的事务使用，其他事

务无法对已锁定的数据进行查询或修改。

        给表加排它锁

        LOCK TABLE table1 WRITE;

        UNLOCK TABLE;

        给数据行加排它锁

        SELECT * FROM table1 WHERE id = 123 FOR UPDATE;

        INSERT、DELETE或者UPDATE的时候，数据库也会自动使用排它锁，防止其他事务对该数

据行进行操作。

      意向锁（Intent Lock）

        给更大一级别的空间示意里面是否已经上过锁。

        意向锁会告诉其他事务已经有事务锁定了表中的某些记录，不能对整个表进行全表扫描。

      乐观锁和悲观锁

        乐观锁：认为对同一数据的并发操作属于小概率事件，不用每次都采用数据库自身的锁机

制，而是通过程序来实现。

        在程序上，采用版本号机制或者时间戳机制实现。

        乐观锁的版本号机制：

        在表中设计一个版本字段version，第一次读的时候，会获取 version 字段的取值。然后对数

据进行更新或删除操作时，会执行UPDATE ... SET version=version+1 WHERE version=version。

此时如果已经有事务对这条数据进行了更改，修改就不会成功。

        乐观锁的时间戳机制：

        在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致

则更新成功，否则就是版本冲突。

        悲观锁：对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保

证数据操作的排它性。

        乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现，不存在死锁问题；

        悲观锁适合写操作多的场景，因为写的操作具有排它性。防止读 - 写和写 - 写的冲突；

 

      死锁的避免和处理

        如果事务涉及多个表，操作比较复杂，那么可以尽量一次锁定所有的资源，而不是逐步来获

取，这样可以减少死锁发生的概率；

        如果事务需要更新数据表中的大部分数据，数据表又比较大，这时可以采用锁升级的方式，

比如将行级锁升级为表级锁，从而减少死锁产生的概率；

        不同事务并发读写多张数据表，可以约定访问表的顺序，采用相同的顺序降低死锁发生的概率。

        处理死锁情况：

        当死锁发生的时候，需要一个事务进行回滚，另一个事务获取锁完成事务，然后将锁释放

掉，很像交通堵塞时候的解决方案。

        1，查询是否锁表

        show OPEN TABLES where In_use > 0;

        2，查询进程（SUPER权限，可以看到所有线程。否则，只能看到自己的线程）

        show processlist; //id - 进程id

        3，查看下在锁的事务

        SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; /trx_mysql_thread_id - 进程id

        4，杀死进程id

        kill id

        查看死锁的命令：

        SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; //查看当前的事务

        SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; //查看当前锁定的事务

        SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; //查看当前等锁的事

务

MVCC

        MVCC是通过数据行的多个版本管理来实现数据库的并发控制，保存数据的历史版本。可以

通过比较版本号决定数据是否显示出来，读取数据的时候不需要加锁也可以保证事务的隔离效果。

通过乐观锁的方式来解决不可重复读和幻读问题；可以在大多数情况下替代行级锁，降低系统的开

销。

        MVCC 可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理

能力，降低了死锁的概率。

      快照读

        一致性读也被称为快照读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点

之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。

        快照读读取的是快照数据。不加锁的简单的 SELECT 都属于快照读，比如这样：

        SELECT * FROM player WHERE ...

      当前读

        当前读就是读取最新数据，而不是历史版本的数据。加锁的 SELECT，或者对数据进行增删

改都会进行当前读，比如：

        SELECT * FROM player LOCK IN SHARE MODE;

        SELECT * FROM player FOR UPDATE;

      MVCC原理

        行记录中有一些重要的隐藏字段：

        db_row_id：隐藏的行ID，用来生成默认聚集索引。创建数据表的时候没有指定聚集索引，

InnoDB 就会用这个隐藏 ID 来创建聚集索引。

        db_trx_id：操作这个数据的事务ID，最后一个对该数据进行插入或更新的事务ID。

        db_roll_ptr：回滚指针，也就是指向这个记录的 Undo Log 信息。

        InnoDB中MVCC是通过 Undo Log、Read View 进行数据读取，Undo Log 保存了历史快照，

而 Read View 规则帮我们判断当前版本的数据是否可见。

        InnoDB将行记录快照保存在了 Undo Log 里，可以在回滚段中找到它们。要找历史快照，就

可以通过遍历回滚指针的方式进行查找。

        Read View保存了当前事务开启时所有活跃的，还没有提交的事务列表。

        

        记录锁：针对单个行记录添加锁。

        间隙锁：锁住一个范围，但不包括记录本身。采用间隙锁的方式可以防止幻读情况的产生。

        Next-Key锁：锁住一个范围，同时锁定记录本身，相当于间隙锁 + 记录锁，可以解决幻读的

问题。

        InnoDB 会采用 Next-Key 锁的机制，解决幻读问题。