Mysql之锁

小表驱动大表

in：使用场景

employee数据量大于department

select * from employee where id in (select id from department)

类似于：

for select id from department d { 
    for select * from employee e where e.id=d.id 
} 

采用的是小表（department）驱动大表（employee）

exists：使用场景

employee数据量小于department

select * from employee e where exists (select * from department d where d.id=e.id)

类似于：

for select * from employee e { 
    for select * from department d where d.id=e.id
} 

采用的是小表（department）驱动大表（employee）

原因：sql查询最耗时的操作为join操作

锁

概念

1、协调多个进程或线程并发访问某一资源的机制

表锁

1、偏向==MyISAM==存储引擎，锁定力度大，加锁快，无死锁，发生冲突概率大

2、整张表只有一个人使用

lock table test read; --让test表加上读锁(共享锁)
lock table test write; --让test表加上写锁（排它锁）
show open tables; --查看加锁信息

unlock tables; --解锁
show open tables;

读锁：共享锁

lock table test read; --让test表加上读锁(共享锁)

	当前连接	另一个链接
是否可以查看自己	可以	可以
是否可以更新	不可以	更新时，处于阻塞状态，等待解锁后更新
能不能读别的表	不可以	可以

写锁：排它锁

lock table test write; --让test表加上写锁（排它锁）

	当前连接	另一个链接
是否可以查看自己	可以	不可以，需要等待写释放
是否可以更新	可以	更新时，处于阻塞状态，等待解锁后更新
能不能读别的表	不可以	可以

行锁

1、偏向偏向==InnoDB==存储引擎，锁定力度小，加锁慢，会出现死锁，发生冲突概率小

2、支持事务

情景①

演示：

set autocommit=0; --先关闭事务

当前链接

update test set name="zs" where id=1

另一个链接：无法查询到最新数据，

原因：当前连接未提交事务！

当前连接执行commit后获取到了最新数据：

情景①

两个链接关闭事务，同时更新相同数据：

• 链接1没有提交事务时，连接2更新处于阻塞状态
• 当连接1进行commit后，连接2更新语句执行，之后进行commit

两个链接关闭事务，更新不同数据：

• 互不影响！

注意：

索引失效，行锁边表锁！

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hG3L1WIU-1606729974408)(C:\Users\acer\AppData\Roaming\Typora\typora-user-images\image-20201130173539002.png)]

索引顺序按name - > age

若更新语句为：

--未按索引最佳左前缀顺序 导致索引失效
① update test set name='zs' where age=10 
② update test set name='zs' where age=30

①更新语句commit执行之前②更新语句将会阻塞！

间隙锁

当前连接：

set autocommit=0
update test set name='zs' where id>3 and id<7

当另一个链接修改（3，7）范围类的数据时，会阻塞，直到当前连接commit

锁定一行数据

要求：当前连接查询数据时，没有commit之前不允许其他链接进行更新

for update：在语句末尾加上

set autocommit=0
select * from employee where id=1 for update

悲观锁

很悲观，认为数据每次被操作时都会修改

关系数据库中的行锁，表锁无论读写锁都是悲观锁！

乐观锁

很乐观，认为数据不会被修改（并发条件下）

无需加锁开销、但是增加了复杂度

实现方式

设计表时，添加一个字段version值设置为1

①版本号 ②时间戳

--首先查询出当前version版本号
select version from employee where id=1  --version=1
--更新操作时将version版本号加1，并且比对当前version是否为查询出来的版本号
--若更新失败，说明该条数据被其他人修改，需要重新查出version版本号，再进行校验更新！
update employee set name='zs',version=1+1 where id=1 and version=1