面试中涉及到的mysql锁看完就不愁了

Rae ·

更新时间:2024-09-21

· 849 次阅读

少讲官话，用最通俗的语言讲清mysql涉及到的锁

MyIsam ：不支持事务，写锁优先，这样会阻塞其他事务的读写，所以myIsam不适合写锁，偏向于用读锁

InnoDB :支持事务（ACID），支持行锁

A: atomicity(原子性) C: consistent（一致性）

I: isolation（隔离性） D: durable（持久性）

一：表级别锁（表锁）

查看表是否加锁情况： show open tables ;

分析总表锁定情况： show status like ‘tables%' ;

检查table_locks_waited和table_locks_immediate两个值的情况分析系统上的表锁定争夺。

table_locks_waited：出现表级锁定争用而发生等待的次数，每等待一次次数加一，如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

table_locks_immediate：产生表级锁定的次数，每次锁定次数加一。

读锁（共享锁）：

加锁： lock table 表一名 read ,表二名 read ...

解锁： unlock tables;

细节：当前加锁的事务，只能读自己（不能写当前表），也不能读写其他表（必须先释放读锁才能执行其他操作）；

其他事务能读当前加锁的表（不能写当前加锁表，写当前加锁的表会是阻塞状态，即要等待之前加锁的事务释放当前表读锁），但是能读写其他表（其他被别的事务加读锁的表也不能写）

写锁（排它锁）：

加锁：lock table 表一名 write,表二名 write...

解锁： unlock tables;

细节：当前加锁的事务，只能对当前加写锁的表进行读和写操作；

被加写锁的表只能由对其加锁的事务进行操作，别的事务不能对其读写，会阻塞

总结：读锁会阻塞其它事务写，写锁会阻塞其他事务读写

二: 行锁（偏向写）

行锁支持事务，对同一行数据进行操作时，后进行操作的事务会被阻塞

注意：当索引的值进行类型转换时，索引会失效，行锁会被转换为表锁（如：索引类型为int,对某行数据的int型索引执行操作时没有加单引号，会导致类型转换，索引失效）

行锁分析： show status like ‘表名%' ;

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

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

InnoDB_row_lock_time_avg ：每次等待所花平均时间

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

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

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

危害：因为查询过程中会锁定条件范围内所有的索引键值，即使不存在的索引键值也会被无辜锁定，而造成在锁定的时候无法插入锁定范围内的任何数据，某些场合危害大

优化建议：

三：页锁(了解)

不常用，甚至于不需了解~~~

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

四：悲观锁和乐观锁

乐观锁：操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。

悲观锁：在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。注：共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴

五：总结

1）表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2）行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
3）页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

所以，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；

行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

mysql的并发操作时而引起的数据的不一致性（数据冲突）：

丢失更新：两个用户（或以上）对同一个数据对象操作引起的数据丢失。

　解决方案：1.悲观锁，假设丢失更新一定存在；sql后面加上for update；这是数据库的一种机制。

2.乐观锁，假设丢失更新不一定发生。update时候存在版本，更新时候按版本号进行更新。