insert mysql 不同session 锁_MySQL锁机制

一、基本概念

从操作的类型上来看，分为读锁和写锁：

读锁：共享锁，对同一份数据，多个读操作可以同时进行且相互间不影响

写锁：排它锁，独占资源。在当前操作未完成之前，其他写操作必须等待。读操作不影响。

排它锁作用于innodb，且必须在事务块中执行。在进行事务操作时，for update会对结果集中的每一行数据加排它锁，其他线程对于结果集中的数据进行修改操作，全部阻塞。

从锁数据的细粒度上来看，分为行锁和表锁。

二、测试

测试环境：mysql 5.5.6、Navicat for mysql。

新建表：

CREATE TABLE `tb_user` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`name` varchar(10) DEFAULT NULL,

`password` varchar(10) DEFAULT NULL,

`sex` char(1) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

开启两个查询会话，模拟多请求。

1、表锁

锁的粒度偏大，开销小，锁表快，但是发生锁竞争的概率特别高，并发度低。

对于更新update、delete、insert自动加写锁。也可如下的显式命令加锁：

基本命令分析：

加锁：LOCK TABLE tablename WRITE/READ;

释放：UNLOCK TABLES;

查询表是否加锁：show open tables;

表锁分析：SHOW STATUS LIKE 'table%';

结果返回两个参数：Table_locks_immediate表示产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。

Table_locks_waited 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)。

1.1、读锁

不阻塞对加锁表的读操作，但是在当前会话中，不可对其他表查询。其他会话的对加锁表的更新，也会阻塞等待锁释放。

-- session01 加表级读锁

LOCK TABLE tb_user READ;

-- session02

-- 查询锁定的表

SELECT * from tb_user;

-- 查询锁定的表

SELECT * from tb_user;

-- 查询其他未锁定表

SELECT * from tbl_user_mycat;

-- 查询其他未锁定表，报错：[Err] 1100 - Table 'tbl_user_mycat' was not locked with LOCK TABLES

SELECT * from tbl_user_mycat;

-- 更新锁定的表，报错：[Err] 1099 - Table 'tb_user' was locked with a READ lock and can't be updated

UPDATE tb_user SET `name`='heihei';

-- 更新锁定的表，会阻塞，直到锁释放后，再继续完成执行操作

UPDATE tb_user SET `name`='heihei';

-- 释放锁

UNLOCK TABLES;

上述更新完成。

1.2、写锁

其他会话中，不能对加锁表进行读写操作。在释放锁之前，也不能对其他未加锁表进行读写操作。

-- session01 加表级写锁

LOCK TABLE tb_user WRITE;

-- session02

-- 查询锁定的表

SELECT * from tb_user;

-- 更新锁定的表

UPDATE tb_user SET `name`='heihei';

-- 查询其他未锁定表

SELECT * from tbl_user_mycat;

-- 查询其他未锁定表，报错：[Err] 1100 - Table 'tbl_user_mycat' was not locked with LOCK TABLES

SELECT * from tbl_user_mycat;

-- 更新锁定的表，会阻塞，直到锁释放后，再继续完成执行操作

UPDATE tb_user SET `name`='heihei';

-- 查询锁定的表,会阻塞，直到锁释放后，再继续完成执行操作

SELECT * from tb_user;

-- 释放锁

UNLOCK TABLES;

上述所有的对加锁的表的读写操作，会执行完成

2、行锁

锁粒度较小(查询结果集的记录行)，发生锁竞争概率较低，并发度高。但是，可能会出现死锁。通常，事务和行锁是在确保数据准确的基础上提高并发的处理能力。

基本命令分析：SHOW STATUS LIKE 'innodb_row_lock%';

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

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

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

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

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

一般来说，关注Innodb_row_lock_waits(等待总次数)、Innodb_row_lock_time_avg(等待平均时长)比较高的时候，说明系统中竞争比较激烈，资源处理慢或者其他什么原因，具体再查询分析结果，制定相关的优化。

innodb在通过索引条件检索数据的时候，会加行锁。否则，都是加的表锁。也就是说：innodb加行锁是针对索引，而不是记录行。没有索引或者索引失效，都会升级为表锁。

对于update、delete和insert语句，innodb会自动的给结果集加排它锁。select默认是不做任何操作的。当然，显式的加锁也是可以滴：

共享锁(读锁，多个读锁可同时进行，但是若事务中对读锁记录做修改操作，很有可能会发生死锁)：SELECT * from tb_user where id=10010 LOCK IN SHARE MODE;

排它锁(写锁，在当前事务未commit之前，阻塞其他的读锁和写锁)：SELECT * from tb_user where id=10010 FOR UPDATE;

以下，2.1和2.2基于update来说明上述的自动加锁机制。2.3和2.4举例来说明显式的共享锁和排它锁问题。

2.1、对于索引列的where，加行锁：

-- SESSION01 开启事务

START TRANSACTION;

-- 更新操作，id为主键，加行锁

update tb_user set `name`='testname' where id=10006;

-- SESSION02 开启事务

START TRANSACTION;

-- 更新操作，id为主键，加行锁。由于和session01锁定不同，所以不阻塞，无需等待session01提交commit，直接执行

update tb_user set `name`='testname3' where id=10010;

COMMIT;

COMMIT;

2.2、对于不是索引列的where，加表锁：

-- SESSION01 开启事务

START TRANSACTION;

-- 更新操作，name为非索引列

update tb_user set `password`='111' WHERE `name`='testname2';

-- SESSION02 开启事务

START TRANSACTION;

-- 更新操作，name为非索引列，由于session01非索引列会锁表，故下面更新会阻塞，等待session01提交commit

update tb_user set `password`='111' WHERE `name`='testname3';

COMMIT;

-- session01提交commit，上述更新update完成操作

COMMIT;

2.3、共享锁

-- SESSION01 开启事务

START TRANSACTION;

-- 查询，显式加共享锁

SELECT * from tb_user where id=10010 LOCK IN SHARE MODE;

-- SESSION02 开启事务

START TRANSACTION;

-- 读锁可直接执行，不阻塞

SELECT * from tb_user where id=10010 LOCK IN SHARE MODE;

-- 对读锁进行修改，等待session02提交commit，阻塞

UPDATE tb_user set name='testname1' WHERE id=10010;

-- 报错：[Err] 1213 -Deadlock found when trying to get lock; try restarting transaction

UPDATE tb_user set name='testname1' WHERE id=10010;

-- 操作继续。session02中，mysql判断出现死锁，回滚session02后，session01继续操作

2.4、排它锁

-- SESSION01 开启事务

START TRANSACTION;

-- 查询，显式加排它锁

SELECT * from tb_user where id=10010 FOR UPDATE;

-- SESSION02 开启事务

START TRANSACTION;

-- 排它锁，阻塞等待session01提交commit，释放锁

SELECT * from tb_user where id=10010 FOR UPDATE;

-- 当前事务中可执行

UPDATE tb_user set name='testname' WHERE id=10010;

-- 排它锁，阻塞等待session01提交commit，释放锁

UPDATE tb_user set name='testname2' WHERE id=10010;

COMMIT;

-- session01提交commit，上述阻塞操作继续执行完成

COMMIT;

2.5、间隙锁

当使用范围查询，且申请共享锁或者排它锁的时候，InnoDB会给符合条件的已有的结果集的索引加锁。对于在范围内但是不存在的的记录值，叫做“间隙(GAP)”。InnoDB加锁也会对这些间隙进行加锁，成为间隙锁。

在锁定一个很大的范围之后，即使记录不存在，也会被锁定。这就导致，如果此时有其他插入这些不存在的记录的请求，会一直阻塞等待。很容易对性能产生影响。

三、使用和总结

1、Innodb默认使用的是行锁。当未使用索引列查询限定的时候会升级为表锁。

一般，在检查分析锁冲突的时候，必须explain查看sql的执行计划。因为mysql在执行过程中，并不是一定会使用索引(explain查看执行计划的时候，才会有possible_key和key)，有可能的情况是mysql认为全表扫描更快，那么此时就不会用行锁，而升级使用表锁。

2、Innodb默认自动给更新操作加锁