MySQL锁机制

# MySQL锁机制

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

锁的分类：

​	1.从对数据操作的类型（读/写）分

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

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

​	2.从对数据操作的粒度分

​		表锁

​		行锁

## 三锁：

### 表锁（偏读）：

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

```mysql
#表级锁分析---建表
USE bilibilipractice;
CREATE TABLE mylock(
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(20)
)ENGINE=MYISAM;

INSERT INTO mylock(NAME) VALUES('a'),
('b'),('c'),('d'),('e');

SELECT * FROM mylock;
#手动增加表锁
LOCK TABLE mylock READ,book WRITE;
#解锁
UNLOCK TABLES;
#查看表是否加过锁    0没加1加了，见下表
SHOW OPEN TABLES;

#加一把读锁
LOCK TABLE mylock READ;
SELECT * FROM mylock;#可以查询到    
#在会话2中也可以查询到

UPDATE mylock SET NAME='a2' WHERE id=1;#报错，错误代码： 1099
#Table 'mylock' was locked with a READ lock and can't be updated
#会话2执行本语句则会阻塞，会话1解锁，会话2获得锁，立马不阻塞，运行。

SELECT * FROM book;#读其他表，错误代码： 1100
#Table 'book' was not locked with LOCK TABLES
#但在会话2中可以查其他表

DELETE FROM mylock WHERE id=1;#报错，错误代码： 1099
#Table 'mylock' was locked with a READ lock and can't be updated
#在会话2中执行本语句会阻塞，会话1解锁，会话2获得锁，立马不阻塞，运行。

#加一把写锁
LOCK TABLE mylock WRITE;
SELECT * FROM mylock;#可以
#会话2执行该语句会阻塞

UPDATE mylock SET NAME='a2' WHERE id=2;#可以
#会话2阻塞

SELECT * FROM book;#报错，错误代码： 1100
#Table 'book' was not locked with LOCK TABLES
#会话2可以执行成功

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

MySQL的表级锁有两种模式：

​ 表共享读锁（Table Read Lock）

​ 表独占写锁（Table Write Lock）

锁类型	可否兼容	读锁	写锁
读锁	是	是	否
写锁	是	否	否

结论：

结合上表，所以对MyISAM表进行操作，会有以下情况：

1.对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。

2.对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

如何分析表锁定？

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定:

SHOW STATUS LIKE 'table%';

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

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

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

行锁（偏写）：

特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁

复习：

事务（Transaction）及其ACID属性：

并发事务处理带来的问题：

1.更新丢失（Lost Update）

2.脏读（Dirty Reads）

3.不可重复读（Non-Repeatable Reads）

4.幻读（Phantom Reads）

事务隔离级别：

案例分析：

1.建表&行锁定基本演示

CREATE TABLE test_innodb_lock(a INT(11),b VARCHAR(16))ENGINE=INNODB;
#建表
INSERT INTO test_innodb_lock VALUES(1,'b2'),
(3,'3'),(4,'4000'),(5,'5000'),(6,'6000'),(7,'7000'),
(8,'8000'),(9,'9000'),(1,'b1');

CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
SELECT * FROM test_innodb_lock;
#会话1
SET autocommit=0;#关闭自动提交事务
UPDATE test_innodb_lock SET b='4001' WHERE a=4;#执行成功

#会话2
SET autocommit=0;
SELECT * FROM test_innodb_lock;

没出现了脏读，会话1修改了数据但未提交在会话2中读不到。（MySQL数据库默认隔离级别是Rr,即可重复读）

#会话1
UPDATE test_innodb_lock SET b='4002' WHERE a=4;
SELECT * FROM test_innodb_lock;

#会话2
UPDATE test_innodb_lock SET b='4003' WHERE a=4;#阻塞

两个会话不修改同一行

#会话1
UPDATE test_innodb_lock SET b='4005' WHERE a=4;#执行成功
SELECT * FROM test_innodb_lock;

#会话2
UPDATE test_innodb_lock SET b='9001' WHERE a=9;#执行成功

2.无索引行锁升级为表锁

#会话1
UPDATE test_innodb_lock SET a=41 WHERE b='4000';#执行成功
UPDATE test_innodb_lock SET a=41 WHERE b=4000;#执行成功,但是字符没加单引号，导致索引失效，行锁变为表锁

#会话2
UPDATE test_innodb_lock SET a=41 WHERE b='4000';#当会话1的字符加单引号了，执行成功，但当没加单引号，此语句会阻塞，因为字符没加单引号，导致索引失效，行锁变为表锁

3.间隙锁的危害

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

#会话1
UPDATE test_innodb_lock SET b='0629' WHERE a>1 AND a<=6;#执行成功

#会话2
INSERT INTO test_innodb_lock VALUES(2,'2000');#理论上应该成功，但是阻塞了

危害：当Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。从而造成在锁定的时候无法插入锁定键值范围内的任何数据。

4.如何锁定一行

#会话1
BEGIN;
SELECT * FROM test_innodb_lock WHERE  a=8 FOR UPDATE;#人为上锁
COMMIT;

#会话2
UPDATE test_innodb_lock SET b='xxx' WHERE a=8;#阻塞

select xxx… for update锁定某一行后，其他的操作会阻塞，直到锁定行提交事务commit

总结：

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量高的时候，InnoDB的整体性能和MyISAM相比就会有明显的优势了。

其次，InnoDB的行级锁一定要使用恰当，要不就适得其反，会让InnoDB的整体性能表现比MyISAM差。

如何分析行锁定：

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';

参数说明：

比较重要的是：

​ Innodb_row_lock_time_avg(等待平均时长)

​ Innodb_row_lock_waits（等待总次数）

​ Innodb_row_lock_time（等待总时长）

当等待次数不高，而且每次等待时间也不小的时候，应该进行分析，进而指定优化策略。

6.优化建议：

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

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

​ 尽可能较少检索条件，避免间隙锁

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

​ 尽可能低级别事务隔离

页锁：

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