mysql数据库锁与隔离级别

数据库锁与隔离级别

内容转载自：

https://blog.csdn.net/sherry_y_fan/article/details/80548560

https://blog.csdn.net/bigtree_3721/article/details/77417518

https://blog.csdn.net/e_online/article/details/6039037

当对数据库数据进行并发读写时，就会出现各种问题（比如事务相关的脏读、幻读；非事务相关的修改丢失等），为了控制这些问题就出现了锁。

• 锁相关
• 事务隔离级别
• 附录

锁

• 乐观锁与悲观锁
• 锁粒度

乐观锁与悲观锁

悲观锁：默认认为对数据的访问一定会产生并发问题，此时只要访问数据就对要访问的数据加锁。当我们使用悲观锁时，通常为了提高并发效率，会将悲观锁细化为读锁与写锁。

• 读锁，又称共享锁，多个连接在同一时间读取同一个资源，二者不相互干扰。
• 写锁，又叫排它锁，一个写锁会阻塞其他的写锁和读锁。

乐观锁： 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测。

注：

• 悲观锁是数据库自身机制来实现的
• 乐观锁是用于在应用层业务来实现的，在数据上添加版本号或时间戳，在业务提交时，判断版本号或时间戳是否是自己读取时的数据

锁粒度

当使用锁时，锁住东西越少，对其他竞争者影响越小，这个东西的大小就是锁粒度。根据锁粒度的大小，将锁分为行锁、表锁、页锁。

MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

MyISAM表锁

• 表锁模式：读写锁的互斥关系
• 如何加锁：MyISAM如何加锁
• 查看锁争用：查看锁争用的程度
• 并发插入：如何同时对一张表读与写
• 锁调度：读写锁加锁的先后顺序

表锁模式

表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。两种模式的互斥关系如下：

如何加锁（两种方式）

1. MyISAM在执行查询语句（SELECT）前，会自动给涉及的**所有表（这是MyISAM不会死锁的原因）**加读锁，在执行更新操作 （UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。
2. 使用lock tables tablename1 read, tablename2 read; 加锁，使用Unlock tables;解锁。在当前session中执行了lock后，该session只能访问加锁的表

查看表级锁争用：

可以查询：table_locks_waited和table_locks_immediate 状态变量来分析系统上的表锁定争夺：

mysql> show status like 'table%';

并发插入

在上面情况下，读和写是串行的。，在一定条件下，MyISAM表也支持查询和插入操作的并发进行。MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

• 当concurrent_insert设置为0时，不允许并发插入。
• 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
• 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

当使用local时，表示支持并发插入参数：

可以利用MyISAM存储引擎的并发插入特性，来解决应 用中对同一表查询和插入的锁争用。例如，将concurrent_insert系统变量设为2，总是允许并发插入；同时，通过定期在系统空闲时段执行 OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。

锁调度

MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后 到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原 因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。

• 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
• 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。
另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

上面已经讨论了写优先调度机制带来的问题和解决办法。这 里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语 句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每 一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

Innodb行锁

关于InnoDB中的锁锁，与MyISAM表锁有较大区别，MyISAM表锁锁定所有的用到的表，所以比较简单。但是在InnoDB中加入了事务，且InnoDB还支持表锁。

如果不开启事务，一条语句执行完成后释放锁，这个锁如果是表锁，估计和MyISAM中表锁一样。如果是行锁，那么由于锁住了最小单位，此时更加简单，出现的只是瞬时的变化。

如果开启了事务，就比较复杂了。下面介绍的都是开启事务后的情况。

• 查询锁争用
• 行锁模式，锁的互斥关系
• 如何加锁
• 间隙锁

锁争用

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';

如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

InnoDB行锁模式

InnoDB上的行锁有共享锁与排他锁。但是由于InnoDB还支持表锁，所以它还协议加两种表锁，意向共享锁与意向排他锁。

• 共享锁（s）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
• 排他锁（Ｘ）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。
• 意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

兼容列表：

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。

如何加锁

• 意向锁是InnoDB自动加的，不需用户干预。

• update,delete,insert都会自动给涉及到的数据加上排他锁

• select 语句默认不会加任何锁类型

• select …for update 语句加排他锁

• select … lock in share mode 加共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

• 只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁，这个需要通过执行计划来确定查询是否走索引！

• MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

• 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

注：所以加过排他锁的数据行在其他事务总是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的 索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求。

如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁，此时锁定的是根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。

锁协议

• 两段锁协议是从加锁、解锁顺序（会影响事务的并发调度）的角度来描述。
• 三级封锁协议是从锁的隔离程度来定义。

两段锁协议

并行操作对并行事务的操作的调度是随机的，不同的调度可能产生不同的结果。在这些不同的调度中，肯定有些调度的结果是正确的，究竟哪些调度是正确的呢？
若每个事务的基本操作都串连在一起，没有其它事务的操作与之交叉执行，这样的调度称为串行调度，多个事务的的串行调度，其执行结果一定是正确的。但串行调度限制了系统并行性的发挥，而很多并行调度又不具有串行调度的结果，所以我们必须研究具有串行调度效果的并行调度方法。
当且仅当某组并发事务的交叉调度产生的结果和这些事务的某一串行调度的结果相同，则称这个交叉调度是可串行化。可串行化是并行事务正确性的准则，一个交叉调度，当且仅当它是可串行化的，它才是正确的。两段锁协议是保证并行事务可串行化的方法。
两段锁协议规定所有的事务应遵守的规则：

• 在对任何数据进行读、写操作之前，首先要申请并获得对该数据的封锁。
• 在释放一个封锁之后，事务不再申请和获得其它任何封锁。

即事务的执行分为两个阶段：

1. 第一阶段是获得封锁的阶段，称为扩展阶段。
2. 第二阶段是释放封锁的阶段，称为收缩阶段。

两段锁协议的事务有可能会发生死锁，为此，又有了一次封锁法。一次封锁法要求事务必须一次性将所有要使用的数据全部加锁，否则就不能继续执行。因此，一次封锁法遵守两段锁协议，但两段锁并不要求事务必须一次性将所有要使用的数据全部加锁，这一点与一次性封锁不同，这就是遵守两段锁协议仍可能发生死锁的原因所在。

三级封锁协议

• 一级封锁协议:事务T在修改数据R之前必须对其加X锁，直到事务结束才释放。以及封锁协议可以防止修改丢失，并保证事务T是可恢复的。在一级封锁协议中，如果仅仅是读数据不对其进行修改，是不需要加锁的，所以它不能保证可重复读和不读“脏”数据。
• 二级封锁协议是:一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁。二级封锁协议除防止丢失修改，还可进一步防止读“脏”数据。在二级封锁协议中，由于读完数据即可释放S锁，所以它不能保证可重复读。
• 三级封锁协议:一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。三级封锁协议可以防止丢失修改，读“脏"数据和不可重复读。三级封锁协议：三级封锁协议的目的是在不同程序上保证数据的一致性。是在一级封锁协议（一级封锁协议：修改之前先加X锁，事务完成释放）的基础上加上，读取之前还要加上S锁，事务结束后释放。

死锁

ＭyISAM表锁是deadlock free的，这是因为ＭyISAM总是一次性获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但是在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了InnoDB发生死锁是可能的。

​ 发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并退回，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获取所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

​ 通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小、以及访问数据库的SQL语句，绝大部分都可以避免。下面就通过实例来介绍几种死锁的常用方法。

1. 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序为访问表，这样可以大大降低产生死锁的机会。如果两个session访问两个表的顺序不同，发生死锁的机会就非常高！但如果以相同的顺序来访问，死锁就可能避免。
2. 程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低死锁的可能。
3. 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应该先申请共享锁，更新时再申请排他锁，甚至死锁。
4. 在REPEATEABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…ROR UPDATE加排他锁，在没有符合该记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可以避免问题。
5. 当隔离级别为READ COMMITED时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第１个线程提交后，第２个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第３个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。

尽管通过上面的设计和优化等措施，可以大减少死锁，但死锁很难完全避免。因此，在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。

如果出现死锁，可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施。

隔离级别

我的理解是数据库的隔离级别是两个事务之间的关系。

• READ UNCOMMITED：事务中修改，未提交，其他事务也能看到
• READ COMMITED：读到其他事务提交的数据，会出现不可重复读。此处的不可重复读包括了脏读（读取一条记录）与幻读（读取某个范围内的记录）
• REPEATABLE READ：可重复读，只解决了脏读（读取一条记录）的问题。但是并没有解决幻读。**MYSQL的默认事务隔离级别是可重复读。**需要注意的是InnoDB存储引擎通过多版本并发控制解决了幻读问题。
• SERIALIZABLE：强制事务串行化执行，也就是会在读取的每一行数据上加锁，导致大量的超时与锁争用。

多版本并发控制MVCC：

1. 大多数的事务型存储引擎不是简单的行级锁，一般他们都是实现了MVCC
2. MVCC是行级锁的变种，很多情况下可以避免加锁操作，因此开销更低。
3. MVCC的实现过程
   • 在每行记录后面保存两个隐藏的列来实现，一个保存了行的创建时间（行的版本号），一个保存了行的过期时间。这两个不是时间，而是系统版本号
   • select查找符合以下两个条件的数据：创建时间早于当前事务版本的数据行。行的删除版本号要么未定义要么大于当前事务版本号
   • insert：插入的行保存当前系统版本号
   • delete：为删除的每一行保存当前系统版本号作为删除标志
   • update：插入一条记录，保存当前系统版本号作为创建时间，同时保存当前系统版本到原来行作为删除标志
4. MVCC只在REPEATABLE READ和READ COMMITTED级别下工作。而READ UNCOMMITED总是读取最新数据行。而SERIALIZABLE会对所有读取的行都加锁

附录