mysql19-锁

1.什么是锁

　　锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

2.锁的分类

2.1从操作来分

2.1.1读锁

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

2.1.2写锁

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

2.2从锁粒度来分

2.2.1表锁

锁定整张表。开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。

2.2.2行锁

只锁定需要的行数据。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

2.2.3说明
　　为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。
　　一种提高共享资源并发发性的方式是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

3.mysql隔离级别

https://www.cnblogs.com/jthr/p/15218682.html

4.MyISAM和InnoDB

mysql中使用比较多的两种引擎是MyISAM和InnoDB。MyISAM只使用表级锁。InnoDB默认使用行级锁，也支持表锁。

4.1MyISAM

　　MyISAM不支持事务。

　　MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。
　　用户也可以用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁（Deadlock Free）的原因。

4.2InnoDB

　　InnoDB支持事务。InnoDB默认使用行锁。InnoDB无索引行锁会升级为表锁

5.MyIsam表锁

5.1表锁语法

1)手动增加表锁

 lock table 表名字1 read(write)，表名字2 read(write)，其它;

2)查看表上加的锁

 show open tables;

3)释放表锁

unlock tables;

5.2示例准备

5.2.1建表

CREATE TABLE `mylock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

这里引擎用的是myisam

5.2.2插入数据

insert into mylock(name,phone,address) values('a','pa','da');
insert into mylock(name,phone,address) values('b','pb','db');
insert into mylock(name,phone,address) values('c','pc','dc');
insert into mylock(name,phone,address) values('d','pd','dd');
insert into mylock(name,phone,address) values('e','pe','de');

5.3读锁示例

5.3.1打开两个session

5.3.2session1

手动给表mylock加读锁

LOCK TABLE mylock READ;

5.3.32session1

查询mylock，可以查询，说明加读锁的当前session可以读取本表

SELECT * FROM mylock;

5.3.4session2

查询mylock，可以查询，说明其它session也可以读取本表

SELECT * FROM mylock

5.3.5session1

查询其他表，不能查询，说明当前session，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表

SELECT * FROM jcustomer;

5.3.6session2

查询其他表，可以查询

5.3.7session1

修改mylock某条数据，不能修改，读锁不允许写

UPDATE mylock SET `name` = 'aa' WHERE id = 1;

5.3.7session2

1）修改mylock某条数据，发现阻塞了，它会等待读锁被释放后再执行

UPDATE mylock SET `name` = 'aa' WHERE id = 1;

2）在session释放锁

UNLOCK TABLES;

3）再看session，发现修改语句已执行成功

4）session2查看修改的数据，发现数据确实已修改

5.3.8session1

1）上面锁释放了，先把读锁再次加上

LOCK TABLE mylock READ;

2）修改其他表，不能修改，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表

UPDATE jcustomer SET custremark1 = 'aa' WHERE CustomerNo = 1;

5.3.9session2

修改其他表，可以修改

UPDATE jcustomer SET custremark1 = 'aa' WHERE CustomerNo = 1;

5.3.10session1

释放锁

UNLOCK TABLES

5.4读锁小结

当前session给某些表(锁表)加了读锁，访问权限小结

	读取锁表	读取其他表	修改锁表	修改其它表
当前session	可以	不可以	不可以	不可以
其它session	可以	可以	阻塞	可以

在执行LOCK TABLES后，当前session只能访问显式加锁的这些表-锁表，不能访问未加锁的表

5.5写锁示例

5.5.1打开两个session

5.5.2session1

给mylock表加写锁

LOCK TABLE mylock WRITE;

5.5.3session1

查询表mylock，可以查询

5.5.4

1）session2：查询表mylock，阻塞了，需要释放锁后才能查询结果

SELECT * FROM mylock;

2）session1：释放锁

SELECT * FROM mylock;

3）再看session2，发现已查询出结果

5.5.5

1）session1添加写锁

LOCK TABLE mylock WRITE;

2）session1查询其它表

不能查询，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表

SELECT * FROM jcustomer;

5.5.6session2查询其它表

可以查询

SELECT * FROM jcustomer;

5.5.7session1

修改mylock表的数据，修改成功

UPDATE mylock SET `name` = 'update_1' WHERE id = 1;

5.5.8

1）session2修改mylock表数据，阻塞了，锁释放后才能执行

2)session1释放锁

UNLOCK TABLES;

3）再看session2，发现已执行成功

5.5.9

1）session1给mylock加写锁

LOCK TABLE mylock WRITE;

2）session1对其它表数据进行修改

不能修改，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表

UPDATE jcustomer SET custremark1 = 'update_1' WHERE CustomerNo = 1;

5.5.10session2

修改其它表，可以修改

UPDATE jcustomer SET custremark1 = 'update_2' WHERE CustomerNo = 1;

5.6写锁小结

当前session给某些表(锁表)加了写锁，访问权限小结

	读取锁表	读取其他表	修改锁表	修改其它表
当前session	可以	不可以	可以	不可以
其它session	阻塞	可以	阻塞	可以

在执行LOCK TABLES后，当前session只能访问显式加锁的这些表-锁表，不能访问未加锁的表

5.7表锁分析

show status like 'table%';

table_locks-waited:需要等待的表锁数

Table_locks_immediate:产生表级锁的次数，可立即释放表锁数
如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎
因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些

6.InnoDB表锁示例

6.1建表

CREATE TABLE `test_innodb_lock` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(16) DEFAULT NULL,
  `c` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

6.2插入数据

insert into test_innodb_lock  values(1,'1000','10');
insert into test_innodb_lock values(3,'2000','20');
insert into test_innodb_lock values(4,'4000','40');
insert into test_innodb_lock values(5,'5000','50');
insert into test_innodb_lock values(6,'6000','60');
insert into test_innodb_lock values(7,'7000','70');
insert into test_innodb_lock values(8,'8000','80');
insert into test_innodb_lock values(9,'9000','90');
insert into test_innodb_lock values(1,'10000','100');

6.3创建索引

create index test_innodb_a_ind on test_innodb_lock(a);
create index test_innodb_lock_b_ind on test_innodb_lock(b);

6.4简要说明

innoDB支持事务，且每一个sql语句默认是自动提交的。一个事务内，对表数据进行编辑，会对编辑涉及到的表的相关行数据加上行锁，特殊情况下特会加表锁，在事务提交后会释放。它的默认的隔离级别是可重复度。

6.4示例1

1)打开两个session

2)session1取消自动提交

SET autocommit = 0;

3）session1查询test_innodb_lock

查询出结果

SELECT * FROM test_innodb_lock;

4）session2查询test_innodb_lock

可以查询

5）session1修改test_innodb_lock数据

执行成功，但是没有commit

UPDATE test_innodb_lock SET b = 'update_1000' WHERE a = 1;

6）session1查询test_innodb_lock

查询成功，且可以看到修改后的数据。说明即便自己还没有commit，也可以查询到修改的数据

SELECT * FROM test_innodb_lock;

7)session2查询test_innodb_lock

查询成功，但是查询到的数据还没有被修改，说明

8）session1提交

COMMIT;

9）session2查询test_innodb_lock

查询成功，查询到的是修改后的数据

SELECT * FROM test_innodb_lock;

10）简要说明

innodb的默认的隔离级别是可重复度，不会出现脏读，一个事务不能读取到另一个事务未提交的数据

6.5示例2

1)打开两个session

2)session1和session2取消自动提交

SET autocommit = 0;

3）session1修改test_innodb_lock数据

执行成功，但是没有commit

UPDATE test_innodb_lock SET b = 'update_3000' WHERE a = 3;

4)session1查询

查询成功，可以查到自己已修改未提交的数据

SELECT * FROM test_innodb_lock WHERE a = 3;

5)session2修改test_innodb_lock

阻塞了，需要session1释放行锁才会执行

UPDATE test_innodb_lock SET c = 'update_300' WHERE a = 3;

6)session1提交

COMMIT;

7）此时查看session2，由于session1已释放行锁，session2不在阻塞，修改的sql已成功执行

8）session2查询

查询成功，可以看到session1已提交的修改数据和自己已修改未提交的数据

SELECT * FROM test_innodb_lock WHERE a = 3;

9)session1查询

查询成功，可以看到session2提交的修改的数据(这里如果查询不到session2修改的数据，就再commit一下再查询)

SELECT * FROM test_innodb_lock WHERE a = 3;

10)说明

一个事务在对数据进行修改时，会对涉及的行自动上行锁，其它的事务对上了行锁的数据的修改会阻塞，等待行锁释放后再执行。

7.5示例3

1)打开两个session

2)session1和session2取消自动提交

SET autocommit = 0;

3）session1修改test_innodb_lock数据

执行成功，但是没有commit

UPDATE test_innodb_lock SET b = 'update_4000' WHERE a = 4;

4）session1查询test_innodb_lock

可以看到自己已修改未提交的数据

5)session2修改test_innodb_lock其它行的数据

成功执行，没有阻塞，证明只有a=4的加了行锁，其它行美影响

UPDATE test_innodb_lock SET c = 'update_500' WHERE a = 5;

6)session2提交再查询

查询成功，可以看到a=5数据已修改，看不到a=4数据的修改，因为session1还没有提交

COMMIT;
SELECT * FROM test_innodb_lock;

7）session提交再查询

查询成功，可以看到a=4，a=5的数据的修改

COMMIT;
SELECT * FROM test_innodb_lock ;

8）说明

行锁锁的是行，对其它行的数据没有有效

7.6示例4

1)打开两个session

2)session1和session2取消自动提交

SET autocommit = 0;

3)session1编辑数据

执行成功，未提交

 UPDATE test_innodb_lock SET b = 'update_6000' WHERE c = '60';

4）session2编辑数据

　　阻塞了。为什么会阻塞呢？session1是对c='60这条数据进行修改'，而这里session2是对a='7'这条数据进行修改，不是同一行数据，为什么a='7'这一行也被锁了呢？这是因为行锁升级为表锁了。

　　InnoDB 行级锁是通过给索引上的索引项加锁来实现的，InnoDB行级锁只有通过索引条件检索数据，才使用行级锁;否则，InnoDB使用表锁在不通过索引(主键)条件查询的时候，InnoDB是表锁而不是行锁。

　　而session1通过c=‘60’来编辑，c是没有索引的，所以加的是表锁。

UPDATE test_innodb_lock SET b = 'update_7000' WHERE a = 7;

5）说明

在没有使用索引的情况下InnoDB就会使用表级锁(共享锁不会有这个情况)

7.7示例5

1)打开两个session

2)session1和session2取消自动提交

SET autocommit = 0;

3)session1编辑数据

执行成功，未提交

 UPDATE test_innodb_lock SET b = 'update_6000' WHERE a > 1 and a < 6;

4）session2编辑数据

执行成功

UPDATE test_innodb_lock SET b = 'update_8000' WHERE a = 8;

5）session2新增数据

插入成功

INSERT INTO test_innodb_lock VALUES (12,'1200','120');

6）session2再次插入数据

　　阻塞了。命名表中都没有这条数据，为什么还会被锁了。这是session1因为使用范围产生了间隙锁。

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

　　session1使用a>1 and a <6,即便没有a=2，这条数据。也会对a=2这个间隙上锁。

INSERT INTO test_innodb_lock VALUES (2,'200','20');

7）说明

　　因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。也就是间隙锁。
　　间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

7.8行锁小结

1) innobd默认使用行锁

2）当我们对表的某些数据进行修改插入时，会默认对这些数据行加上行锁排它锁

3）在没有使用索引的情况下InnoDB就会使用表级锁(排它锁才会这样)

4）当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，也就是间隙锁

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

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：系统启动后到现在总共等待的次数；
对于这5个状态变量，比较重要的主要是
Innodb_row_lock_time_avg（等待平均时长），
Innodb_row_lock_waits（等待总次数）
Innodb_row_lock_time（等待总时长）这三项。
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

2）可以通过下面语句查询正在被锁阻塞的sql语句。

SELECT * FROM information_schema.INNODB_TRXG

3）查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

4）查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

5）查看表使用情况

SHOW OPEN TABLES;　　列举在表缓存中当前被打开的非TEMPORARY表

SHOW OPEN TABLES In_use > 0;

Table:表名称。

In_use:表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。

Name_locked:表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作

7.10手动加上锁

1）读锁
　　共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

用法

SELECT ... LOCK IN SHARE MODE;

2）写锁
排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

用法

SELECT ... FOR UPDATE;

在查询语句后面增加 FOR UPDATE ，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。