mysql的锁机制

一丶简介

mysql针对不同存储引擎有不同类型的锁。MyISAM和MEMORY存储引擎采用的是表级锁；InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下优先采用行级锁，无法用行级锁时会使用表级锁
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

可以通过检查table_locks_waited来分析系统上的表锁定争夺，值越高表锁争夺越严重：
show status like ‘table_locks%’;

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况，InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值越高行锁争夺越严重：
show status like ‘innodb_row_lock%’;

二、InnoDB存储引擎中的锁

共享锁（s）：又称读锁，当数据对象被加上共享锁后，所有事务能对其进行读取操作，但不能修改
排它锁（x）：又称写锁，当一个事务对数据对象加上排他锁后，其他事务不能对其加锁操作，但获取了排它锁的事务可以对数据对象进行修改与读取操作
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁。

mysql InnoDB中，mysql默认会对insert、update、delete加上排它锁，加过排他锁的数据其他事务不能对其进行修改，也不能加其他锁，但可以直接通过select…from…查询数据，因为普通查询没有任何锁机制。select语句默认不会加任何锁，如果要加排它锁可以使用select…from…for update，如果要加共享锁可以使用select…from…lock in share mode

InnoDB锁的演示
演示数据库版本为8.0，创建数据库并插入数据

create table test (id int, name varchar(10)) engine=innodb; 
insert into test values(1,'1'),(2,'2'),(3,'3'),(4,'4');

打开两个mysql会话窗口，关闭自动提交

 set @@autocommit = 0;

1.不通过索引条件查询
mysql加行级锁的实现方式是通过对索引上的索引项来加锁的，所以当检索条件上有索引时，才会使用行级锁，否则使用的是表锁

会话1：select * from test where id = 1 for update; // 查到了数据
会话2：select * from test where id = 2 for update; // 虽然查询的是两条记录，但没有返回查询结果，并一直阻塞，表示此时未使用行锁，而是用的表锁
会话1：commit; // 此时会话2显示了查询的结果
会话1：select * from test where id = 1; // 此时虽然是会话2拿到了锁，但这个查询语句没有任何加锁机制，同样也能查到数据

2.通过索引条件查询
给表创建索引

alter table test add index id(id);

会话1： select * from test where id = 1 for update; // 查到了数据
会话2： select * from test where id = 2 for update; // 查到了数据，所以通过索引条件查询数据时并不会锁住整个表

3.通过相同的索引条件查询不同数据
插入数据

insert into test  values(1,'4');

会话1： select * from test where id = 1 and name = ‘1’ for update; // 查到了数据
会话2： select * from test where id = 1 and name = ‘4’ for update; // 虽然访问不同的数据，但由于行锁是针对索引加锁，所以同样会阻塞，直到拿到了锁

4.死锁演示
会话1： select * from test where id = 1 for update;
会话2： select * from test where id = 2 for update;
会话1： select * from test where id = 2 for update;
会话2： select * from test where id = 1 for update;
此时，会话2报错ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction，两个会话都退出阻塞状态。

三丶MyISAM存储引擎中的表锁

mysql共有两种表级锁：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

对MyISAM表的读操作，不会阻塞对同一表的读请求，但写操作会阻塞对同一表的读和写操作，MyISAM在执行读操作时会自动加表共享读锁，写操作时会自动加表独占锁

MyISAM表锁演示
下面的加锁操作都是为了演示效果，创建数据库并插入数据

CREATE TABLE `lock_test` ( `id` int(11) PRIMARY KEY, `name` varchar(20) ) ENGINE=MyISAM;
insert into lock_test values(1,'1'),(2,'2'),(3,'3'),(4,'4');

1.表独占写锁演示
会话1：
lock table mylock write; // 获取表独占写锁
select * from lock_test;
insert into lock_test values(5,‘e’); // 读写操作都可以
会话2：select * from lock_test; // 查询被阻塞
会话1： unlock tables; //释放锁，此时会话2就能查询到数据

2.表共享读锁演示
会话1：
lock table lock_test read; // 获取共享读锁
select * from lock_test; // 能查到该表数据
insert into lock_test values(6,‘f’); // 新增数据出错
update lock_test set name = ‘a1’ where id = 1; // 修改数据出错
delete from lock_test where id = 1; // 删除数据出错
select * from test; // 查询其他表出错
会话2：
select * from lock_test; // 能查到该表数据
insert into lock_test values(6,‘f’); // 新增数据时被阻塞
会话1：unlock tables; // 释放锁后，会话2就成功新增数据

3.读写并行演示
上述操作的读写操作都是串行的，MyISAM是支持读和插入操作并行的
会话1：lock table mylock read local; // 获取read local锁
会话2：
insert into lock_test values(7,‘g’); // 插入数据成功
delete from lock_test where id = 1; // 删除数据被阻塞
会话1：unlock tables; // 释放锁后，会话2就成功删除数据

四、总结

MyISAM

1. MyISAM默认的锁调度机制是写优先，可以在语句中指定LOW_PRIORITY让写锁的优先级降低，减少读写操作的争用，如果要将允许设置LOW_PRIORITY的语句的优先级都降低，可以通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。还有一种通过设置max_write_lock_count的值来折中调节读写冲突，当读锁达到这个值后，mysql会将写锁的优先级降低

InnoDB

1. 在不同程序访问多个表时，应尽量保证相同顺序访问各个表；多个线程对一个表时，尽可能以固定顺序处理数据，这样可以大大避免死锁
2. 在更新操作中，应一次性申请好足够量级的锁，例如，不要先申请共享锁，再申请排他锁，这样容易出现死锁
3. 尽量使用索引来访问数据，减少锁冲突
4. 选择合理的事务，级别越小的事务，发生锁冲突越低