mysql锁机制 php_MySQL锁机制

1 定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。

2 分类

①按照对数据操作的类型分：读锁和写锁

读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会相互影响。

写锁(排它锁)：当前写操作没有完成前，它会阻断其他写锁和读锁。

②按照对数据操作的粒度分：表锁和行锁

3 三种锁

3.1 表锁(偏读)

①特点

偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。

②相关指令

手动增加表锁：lock table 表名字 read(write), 表名字2 read(write), 其他;

查看表上加过的锁：show open tables;

释放表锁：unlock tables;

③加读锁：读阻塞写

Session 1	Session 2
获得表的READ锁定	连接终端
当前session可以查询该表记录	其他session也可以查询该表记录
当前session不能查询其他未锁定的表	其他session可以查询或更新未锁定的表
当前session插入或更新锁定的表会提示错误	其他session插入或更新锁定表会一直等待获得锁
释放锁	session2获得锁，插入操作完成

④加写锁：写阻塞读

Session 1	Session 2
获得表的WRITE锁定	待session 1开启写锁后，session 2再连接终端
当前session对锁定表的查询、更新、插入操作都可以执行	其他session对锁定表的查询被阻塞，需要等待锁被释放
释放锁	session2获得锁，查询返回

⑤总结

MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的所有表加写锁。

读锁会阻塞写，但不会阻塞读，写锁则会将读和写都堵塞。

MyISAM的读写锁调度是写优先，因此MyISAM不适合做写为主表的引擎，因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久阻塞。

⑥分析表锁定

show status like ‘table%’;有两个状态变量记录MySQL内部表级锁定情况

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。

Table_locks_immediate：出现表级锁定正在等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在着较严重的表级锁争用情况。

3.2 行锁(偏写)

①特点

偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度最高。支持事务。

②行锁定基本演示(InnoDB更新数据自动上锁)

Session 1	Session 2
Set autocommit=0;	Set autocommit=0;
session1更新第4行数据但不提交记录	session2更新第4行数据被阻塞，只能等待
session1提交更新记录	session2解除阻塞，更新正常进行
session1更新第4行数据但不提交记录，不影响session2	session2更新第9行数据但不提交记录，不影响session1
session1提交更新记录	session2提交更新记录

③索引失效，行锁变表锁

④间隙锁

定义：当我们用范围条件而不是相等条件检索数据，并请求共享或排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但是并不存在的记录，叫做“间隙(GAP)”，InnoDB会对这个“间隙”也加锁，这种锁机制称为间隙锁(Next-key锁)。

危害：当锁定一个范围键值之后，即使某些不存在的键值也会被无辜锁定，而造成在锁定的时候无法插入键值范围内的任何数据，在某些场景下会对性能造成很大的危害。

⑤如何手动锁定指定的一行数据

Set autocommit=0;

begin;

select * from 表名 where a=8(某一行) for update;

commit;

select XXX for update锁定某一行后，其他操作会被阻塞，直到锁定行的会话提交commit

⑥总结

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能会比表级锁定更高，但在整体并发处理能力方面远远优于MyISAM的表级锁定。当系统并发量较高的时候，InnoDB的整体性能优势非常明显，但是若InnoDB的行级锁定使用不当，其整体性能可能不如MyISAM。

⑦分析行锁定

show status like ‘innodb_row_lock%’;有五个状态变量记录行锁的争夺情况

Innodb_row_lock_current_wait：当前正在等待锁定的数量。

Innodb_row_lock_time：从系统启动到现在锁定总时间长度。*

Innodb_row_lock_time_avg：每次等待所花平均时间。*

Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间。

Innodb_row_lock_waits：从系统启动到现在总共等待的次数。*

当等待次数很高，且每次等待时长也不小的时候，就需要分析系统中为什么有如此多的等待，然后根据分析结果进行优化。

⑧优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁；

• 合理设计索引，尽量缩小锁的范围；

• 尽可能减少检索条件，避免间隙锁；

• 尽量控制事务大小，减少锁定资源量和时间长度；

• 尽可能低级别事务隔离。

3.3 页锁(了解)

特点

开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定粒度介于表锁和行锁之间，并发度一般。

杨馨怡，重庆大学无线通信技术实验室硕士研究生，主研方向为高速移动场景预编码技术。