mysql高级----锁机制（表锁、行锁；读写锁；乐观锁，悲观锁）

一、锁介绍

１、定义

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

在数据库中，除传统的计算资源（如ＣＰＵ、ＲＡＭ、Ｉ／Ｏ等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言尤其重要，也更加复杂。

 

２、举个例子（生活购物）

 

３、锁的分类

＃３.1从对数据操作的类型来分

• 读锁（共享锁）:针对同一份数据，多个读操作可以同时进行而不会互相影响。（
• 读锁场景：session１中，给Ａ表加上读锁    

 

操作类型\事务	session1	session2	错误样例截图
select * from A	OK	OK
select * from B	报错	ＯＫ
insert into A values.. update A set ...	报错	进入阻塞状态
session1中　unlock tables	OＫ	得到资源插入or更新成功

 

 

 

    

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

 

 

＃3.2 从对数据操作的粒度来分：行锁和表锁

 

＃３.3 补充知识:

＃悲观锁：进行业务操作前先加锁，即一锁二查三更新。

＃乐观锁：乐观锁的特点先进行业务操作，不到万不得已不去拿锁。

即“乐观”的认为拿锁多半是会成功的，因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。乐观锁不依赖于数据库的具体功能，而是通过代码的业务逻辑来实现乐观锁功能。

1. SELECT data AS old_data, version AS old_version FROM …;

2. 根据获取的数据进行业务操作，得到new_data和new_version

3. UPDATE SET data = new_data, version = new_version WHERE version = old_version

 

if (updated row>0) {

// 乐观锁获取成功，操作完成

} else {

// 乐观锁获取失败，回滚并重试

}

乐观锁是否在事务中其实都是无所谓的，其底层机制是这样：在数据库内部update同一行的时候是不允许并发的，即数据库每次执行一条update语句时会获取被update行的写锁（这句话的意思是数据库在更新一条数据时，会自动加上写锁），直到这一行被成功更新后才释放。因此在业务操作进行前读取出数据，并将此版本号一同读出，之后更新时，对此版本号加一。此时，将old_version做为updated的条件，如果update row>0，表示更新成功，提交事务；否则认为未找到old_version的数据，更新失败，回滚并重试。

 

 

小结：

• 乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能
• 乐观锁还适用于一些比较特殊的场景，例如在业务操作过程中无法和数据库保持连接等悲观锁无法适用的地方

 

二、表锁(MyISAM存储引擎默认的锁类型）

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

＃案例准备

１、表结构

 

２、手动增加表锁

语法格式：lock table 表名 read/write,表名２ read/write...

以上表为例：lock table mylock read

                   lock table mylock write

 

３、查看表上过的锁

语句：show open tables;

 

４、释放表锁（终止占用锁的会话　or  执行语句unlock tables）

 

1  调用unlock tables，必须由执行Lock tables的同一会话执行

2  若会话再次调用lock tables，则其原本获取到的锁会被隐式释放

3   开启事务会隐含调用unlock tables，诸如start transaction

4   占有锁的会话一旦终止其锁会被自动释放，事务也会被回滚

5   rollback不会释放表锁

 

＃案例分析

 

案例一：读锁

场景：session１中，给Ａ表加上读锁    

操作类型\事务	session1	session2	错误样例截图
select * from A	OK	OK
select * from B	error 1100	ＯＫ
insert into A values.. update A set ...	error 1099	wait 进入阻塞状态
insert into B values.. update B set ...	error 1100	ＯＫ
session1中　unlock tables	OK	得到资源插入or更新成功

案例二：写锁

 

场景：session１中，给Ａ表加上写锁

操作类型\事务	session1	session2	错误样例截图
select * from A	OK	进入阻塞状态
select * from B	error 1100	ＯＫ
insert into A values.. update A set ...	ＯK	进入阻塞状态
insert into B values.. update B set ...	error 1100	ＯＫ
session1中　unlock tables	OK	得到资源插入or更新成功

＃案例结论

简而言之，读锁会阻塞写，但不会阻塞读；而写锁会阻塞读跟写。

 

＃表锁分析

show status like 'table%'

 

 

 

 

三、行锁

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

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

 

＃知识点复习

１、事务及ＡＣＩＤ特性

 

２、并发事务处理带来的问题

2.1 更新丢失

2.2 脏读

 

2.3 不可重复读

2.4 幻读

幻读：读到其它事务新增数据

脏读：读到其它事务修改未提交的数据

 

３、事务隔离级别

• read-uncommited（读未提交）
• read-commited（读提交）
• repeatable-read（可重复读）>>>mysql默认的事务级别
• serializable(可序列化）

 

＃＃查看当前事务的隔离级别：show variables like 'tx_isolation'   

 

＃案例准备

１、表结构

 

２、关闭自动提交

set autocommit=0;

 

 

＃案例分析

>>无索引会导致行锁升级为表锁

 

>>间隙锁的危害(session1提交事务后，session2才能插入数据）

【１、什么是间隙锁？？】

【２、间隙锁的危害】》》宁可杀错，不可放过。

因为query 在执行过程中通过范围查找的话，会锁定事个范围内所有的索引键值，即使这个键值并不存在。

所以当session1锁定１～６内的键值时，session2无法插入此键值范围内a=2的数据

 

>>面试题：如何手动给一条记录加上行锁？

用select * from xxx for update

 

＃案例结论

 

＃行锁分析

show status like 'innodb_row_lock%'

参数说明：

 

＃优化建议