mysql锁竞争_十四、MySQL中的锁机制 － 系统的撸一遍MySQL

之前在上文已经记录过锁的简单应用今天更深入的了解一下MySQL中的锁。

锁的类型

锁类型

存储引擎

特点

表级锁

MyISAM、MEMORY、InnoDB(非默认)

开销小，加锁快，不会死锁，颗粒大，并发低。

行级锁

InnoDB

开销大，加锁慢，会死锁，颗粒小，并发高。

页面锁

BDB

介于以上两种之间，会死锁。

MyISAM中的表级锁

锁的模式

表级锁支持两种模式，共享读锁和独占写锁。

共享读锁

READ锁定后，当前线程不可以写入，其他线程写入会被阻塞到解锁后执行，查询不受影响。

独占写锁

WRITE锁定后，当前线程可以插入查询，其他进行插入查询会被阻塞到解锁后执行。

查看表等待数量

//查看表等待次数

show status like 'table%';

一般会体现在 table_locks_waited中，如果比较高说明发生大量锁等待。

锁的用法

MySQL执行读写操作会自动进行锁定，一般不需要显示指定。

锁的使用方法可以参考：锁的简单应用

LOCK TABLES table_name READ LOCAL;

LOCAL表示允许获取读锁的时候在表结尾进行并发插入。

MySQL可以通过 concurrent_insert 来控制并发插入。

concurrent_insert 为 0 的时候不允许并发插入。

concurrent_insert 为1 的时候，如果表中不存在空洞可以在结尾插入。

concurrent_insert 为 2 的时候，是否有空洞都允许结尾插入。

锁的调度

如果加锁的时候已经被其他线程锁定，则会阻塞到其他线程解锁。

如果两个进程同时进行读锁和写锁的请求，那么写锁会优先执行，哪怕读锁先到。

我们可以通过 low-priorty-updates 参数来设置优先级别。

SET LOW_PRIORITY_UPDATES=1;

SET LOW_PRIORITY_INSERT=1;

SET LOW_PRIORITY_DELETE=1;

通过设置来降低写的优先级。

同时还可以通过设置 max_write_lock_count 来实现锁等待超过一定数量后读优先，用于避免大量写操作导致查询阻塞。

InnoDB中的行几锁和事务

事务的ACID原则

原子性

事务作为一个单独的处理单元具有原子性，要么全部执行，要么全部不执行

一致性

事务的开始和结束都保持一致状态，事务结束的时候必须保证数据是正确的。

隔离性

并发事务的情况下，不允许互相影响，每个事务必须是隔离开的。

持久性

事务一旦提交便永久生效。

并发事务出现的问题

更新丢失

两个并发事务A和B，A和B同时读取了数据，A修改数据提交后，B页修改数据进行提交，会覆盖A种修改的数据。

脏读

两个并发事务A和B，A读取并修改了数据但是没有提交，B此时读取了A修改后的数据并提交，此时A回滚了数据，那么B提交的数据会成为脏数据。

不可重复读

一个事务读取数据后，再次读取数据(同一条数据)发现两次数据不一致。

幻读

一个事务读取数据后，再次读取数据(相同条件读取的数据)，发现其他事务插入了满足条件的新数据。

事务的4种隔离机制

未提交读

通过排他锁避免更新丢失。

这个级别的事务可以读取到其他事务的未提交数据，所以会产生脏读。

已提交读

通过排他锁和共享空间锁来避免更新丢失和脏读。

这个级别的事务可以读取到其他事务已经提交的数据，所以多次读取会出现“不可重复读”。

可重复读

通过排他锁和共享空间锁来避免更新丢失和脏读，在进行读锁的时候禁止写锁执行，写锁的时候禁止任何锁执行，用于避免重复读。

可序列化

最为严格的隔离机制，通过串行的方式保证事务一个一个执行。

//查看隔离级别

select @@global.tx_isolation,@@tx_isolation;

查看InnoDB锁状态

show status like 'innodb_row_lock%';

通过查看返回结果中 锁等待次数和平均等待时间来判断当前锁状况，如果比较高说明当前锁竞争严重，可以通过查看 schema.innodb_locks来进一步查询。

InnoDB的行锁

共享锁(S)：对一行数据开启共享锁，会阻止其他数据对这行数据开启排他锁。

排他锁(X)：获得排他锁的数据可以写数据，其他数据不可以获得共享锁或排他锁。

为了兼容表级锁，实现了以下两种锁。

意向共享锁(IS)：事务得到共享锁之前先取得该锁。

意向排他锁(IX)：事务得到排他锁之前先取得该锁。

加锁的模式

InnoDB种不需要显式的加锁，会在执行语句时候自动加锁解锁。

InnoDB行锁通过给索引上的索引项加锁，没有索引会加载聚集索引上。

三种索引方式

1、Record lock：直接对索引项加锁

2、Gap lock：对索引项第一条和最后一条的位置加锁(GAP锁)用于防止幻读(RC、RR隔离级别)。

3、Next-key lock：两者结合

InnoDB加锁如果使用索引(非主键)，会同时给索引项和聚集索引项加锁。

范围查询的时候会锁住范围内的所有索引项。

注意：如果检索数据没有通过索引条件检索(全表扫描)，那么将对表中所有数据加锁。