mysql锁原理_MySQL锁原理浅谈

锁类型/引擎

行锁

表锁

页锁

MyISAM

有

InnoDB

有

有

BDB(被InnoDB取代)

有

有

锁的分类

表锁：开销小，加锁快，不会死锁，粒度大，冲突率高，并发低。

行锁：开销大，加锁慢，会死锁，粒度小，冲突率低，并发高。

页锁：处于表锁和行锁之间，会死锁。

锁的适用场景

表锁：更适用于查询为主，按少量索引条件更新。

行锁：更适用于大量按索引并发更新少量不同数据，同时又有并发查询。

MyISAM表锁

查看锁争用相关参数：show status like 'table%';

Table_locks_waited的值越高表示表锁争用越高。

MyISAM表的读操作，会阻塞同表的其他读请求，会阻塞同表写请求；

写操作会阻塞同表的读请求和写请求。

读与写、写与写之间串行，持锁线程可对表更新，其他线程读/写都会等待，直到锁释放。

MyISAM写阻塞读的例子

session 1

session 2

lock table user write;

select * from user; //返回查询结果

select * from user; //被阻塞，等待锁被释放

unlock tables;

获得锁，返回查询结果

注：

lock tables时，要一次性锁定用到的所有表

对别名也需要锁定，如：lock table user as a read, user as b read;

MyISAM读阻塞写例子

session 1

session 2

lock table user read;

可查询：select * from user;

可查询：select * from user;

不能查询未锁定的表：select * from goods; //Table 'goods' was not locked with Lock Tables

能查询/更新未锁定的表

当前session更新锁定表会报错，Read Lock

更新锁定表会等待

Unlock tables;

获得锁，更新完成

MyISAM并发插入

系统变量 concurrent_insert：用于控制并发插入行为

0 不允许并发插入

1 表中没有被删除的行(即没有空洞)，则允许一个进程读，另一个进程在表尾插入(默认设置)

2 表中不论是否存在空洞，都允许在表尾并发插入

MyISAM读写并发

session 1

session 2

lock table user read local;

当前session无法对该表更新或插入

可以插入，但更新需要等待锁释放

无法访问其他session插入的数据

unlock tables;

获得锁，更新完成

可以查到其他session插入的数据

注：

利用并发插入可以解决应用对同一个表查询和插入的锁争用；

将cocurrent_insert设置为2，定期OPTIMIZE TABLE来整理空间碎片，回收删除记录产生的空洞。

MyISAM锁调度

读锁与写锁互斥；

读操作与写操作串行；

写进程先获得锁，即使读请求先到队列，也会被写请求插队，因为mysql认为写比读要重要(因此MyISAM不适合有大量更新/插入操作)。

调节MyISAM锁调度行为

low-priority-updates，给予读优先权利；

SET LOW-PRIORITY_UPDATES=1，降低更新请求优先级；

指定INSERT、UPDATE、DELETE的LOW-PRIORITY属性，降低该语句优先级。

解决读写冲突的方法：

系统参数 max_write_lock_count 设置合理值，表的读锁达到设定阈值后，mysql就将写请求优先级降低。

一些需要长时间运行的读操作，需要拆分为多条短select sql，复杂查询放在数据库空闲时段进行，比如夜间执行。

InnoDB与MyISAM最大区别：

支持事务；

行级锁。

事务 - Transaction

事务操作

描述

BEGIN 或者 START TRANSACTION

开始事务

COMMIT

提交事务

ROLLBACK

回滚结束事务，撤销进行中的所有未提交的修改

SAVEPOINT identifier

设置保存点

RELEASE SAVEPOINT identifier

事务回滚到保存点

ROLLBACK TO identifier

撤销保存点

SET TRANSACTION = {READ UNCOMMITED，READ COMMITED，REPEATABLE READ，SERIALIZABLE}

设置事务隔离级别

SET AUTOCOMMIT = {0，1}

禁止/开启自动提交

事务的特性

A - Atomicity 原子性：全执行/全不执行

C - Consistent 一致性：数据状态一致

I - Isolation 隔离性：事务处理过程中的中间状态对外不可见，不受外部并发操作影响

D - Durable 持久性：事务完成后对数据修改是永久性的

并发事务问题

描述

解决方案

更新丢失

两个事务对同一行数据修改，先提交的被后提交的覆盖

应用程序对要更新的数据加锁

脏读

A事务改一行数据，B事务读到了A的改动“脏”数据，A回滚则B的数据有问题

数据库事务隔离，解决读一致性问题：1、读之前加锁，防止其他事务对数据修改；2、不加锁，生成快照，多版本并发控制

不可重复读

一个事务多次读取同一数据发现被改变/删除

同上

幻读

一个事务按先前的条件查询，发现其他事务插入了满足条件的新数据

同上

注：

事务隔离级别越高，并发副作用越小，代价越高，因为事务隔离从某种程度上说使得事务串行化。

MySQL事务隔离级别

隔离级别/并发问题

读一致性

脏读

不可重复读

幻读

未提交读

最低

有

有

有

已提交读

语句级

无

有

有

可重复读

事务级

无

无

有

可序列化

最高

无

无

无

获取InnoDB行锁争用情况

show status like 'innodb_row_lock%';

锁争用严重时，InnoDB_row_lock_waits和InnoDB_row_lock_time_avg值较大。

InnoDB行锁类型

行锁类型

描述

共享锁 S

允许事务读一行，阻止其他事务获得排他锁

排他锁 X

允许事务更新数据，阻止其他事务获得共享读锁和排他写锁

意向共享锁 IS

事务打算给行加共享锁，先取得表IS锁

意向排他锁 IX

事务打算给行加排他锁，先取得表IX锁

请求锁模式是否兼容当前锁模式

X

IX

S

IS

X

否

否

否

否

IX

否

是

否

是

S

否

否

是

是

IS

否

是

是

是

注：

含I的锁与含I的锁兼容；

单X与任何锁不兼容；

单S与含X的锁不兼容；

若一个事务请求的锁模式与当前的锁兼容，InnoDB将请求的锁授予该事务，不兼容就要等到锁释放；

意向锁是InnoDB自动加的，DELETE、UPDATE、INSERT，InnoDB会自动加X锁，普通SELECT，InnoDB不加任何锁。

手动加锁的方法

共享锁(S)：SELECT * FROM user LOCK IN SHARE MODE;

排他锁(X)：SELECT * FROM user FOR UPDATE;

注：

SELECT * FROM ... LOCK IN SHARE MODE; //若当前事务加了读锁，进行更新会死锁

SELECT * FROM ... FOR UPDATE; //一个事务加了写锁，其他事务加锁操作需要等待

InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索，才会使用行级锁，否则会用表锁；

分析锁冲突时，检查SQL执行计划(利用explain)，以确认是否真正走了索引，例如：SELECT * FROM user WHERE name = 123; //name字段是varchar类型且有索引，但条件中用了int型，类型能自动转换，但会进行全表扫描。

间隙锁(Next-key Lock)

概念描述

用范围而非等值搜索数据，并且请求共享/排他锁时，InnoDB会对所有符合条件的已有记录的索引项加锁，对键值在范围内但不存在的记录，即GAP-间隙，也会加锁。

例如：

user表，id从1~100共100个，执行：

SET AUTOCOMMIT = 0;

SELECT * FROM id > 99 FOR UPDATE;

会对id等于100的记录的索引项加锁，对id大于99的间隙加锁。

作用：

满足隔离级别要求，防止幻读；

满足恢复和复制需要(MySQL通过BINLOG录入执行成功的INSERT、UPDATE、DELETE等更新语句)

存在的问题：

按范围加锁机制会阻塞符合条件范围内的键值并发插入，造成锁等待。

解决方法：

优化业务逻辑，尽量用相等条件来检索数据。

注：

相等条件检索一个不存在记录加锁时，InnoDB也会使用间隙锁。例如：

对上面的user表，执行：

SET AUTOCOMMIT = 0;

SELECT * FROM id = 101 FOR UPDATE;

再在另一个 MySQL Session 中执行 INSERT INTO user (id, name, password, description)

VALUES

(101, 'clive', '123456', 'psw'); //查询被阻塞，进入等待直至锁释放

死锁的概念

死锁是指多个事务在统一资源上，出现相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。

MyISAM和InnoDB在死锁上的区别

MyISAM不会出现死锁，因为MyISAM总是一次获得所需要的全部锁，要么全部满足，要么全等待；

InnoDB除了单SQL事务，锁是逐步获得的，因此可能出现死锁。一般InnoDB能自动检测死锁，并使一个较简单的事务回退并释放锁，另一个事务获得锁，继续完成事务。