本文知识点来源于网络,仅限学习笔记使用,记录学习过程与心得,如有不适,请联系。
一、表锁
偏向MyISAM 存储引擎,开销小,加锁快,无死锁,锁定力度大,发生锁冲突的概率最高,并发最低。
先看几条常用sql:
#查看表有没有被锁
SHOW OPEN TABLES;
SHOW OPEN TABLES WHERE in_use > 0;
#给表加读锁
LOCK TABLE 表名 READ;
#给表加写锁
LOCK TABLE 表名 WRITE;
#对表解锁
UNLOCK TABLES;
新建表table_locks_test,使用MyISAM存储引擎。
1.1 表锁之共享锁(读锁)
首先利用Navicat 连接mysql数据库,模拟一个用户(数据库连接)我们成为用户1
然后,我们打开命令提示符,创建一个连接,我们成为用户2
用户1先执行加读锁
#给表加读锁
LOCK TABLE table_locks_test READ;
我们查看用户1能不能查询table_locks_test表
SELECT * FROM table_locks_test;
我们发现可以
再看用户1能不能查询其它表
SELECT * FROM employee;
我们通过提示发现不能
再看用户1能不能进行写操作
INSERT INTO table_locks_test(name,age) VALUES('张三',20);
我们发现也不能
再看用户2
先看用户2能不能查看被读锁锁的table_locks_test
SELECT * FROM table_locks_test;
我们发现可以只是该表没有记录
我们再看用户2能不能查看其它表
我们发现用户2可以查询其它表
我们再看用户2能不能对table_locks_test进行写操作
我们发现用户2执行insert操作后,阻塞在了这里
下面我们让用户1释放锁,注意观察用户2的变化
我们发现用户一解锁的同时,用户2的阻塞也释放开,并且成功的执行了insert操作,我们在让用户2查询该表,发现确实数据插入了。
总结:
现有A,B两个数据库连接,A连接给表加共享锁(读锁)时:
1. A连接可以查看被锁的表,B连接也可以查看,即共享
2. A连接不可以执行写操作,B连接也不可以执行写操作,但是会处于阻塞状态,等待解锁后,会立即执行被阻塞的写操作。
3. A连接不可以查看其它表,B连接可以查看其它表。
1.2 表锁之排他锁(写锁)
首先我们让用户1给table_locks_test上写锁
#给表加写锁
LOCK TABLE table_locks_test WRITE;
我们看用户1能否查询table_locks_test表数据
SELECT * FROM table_locks_test;
没有问题,用户1可以执行可以查询table_locks_test表数据
再看用户1能否对table_locks_test执行写操作
INSERT INTO table_locks_test(name,age) VALUES('李四',25);
没问题,用户1可以对table_locks_test执行写操作
再看用户1能否查询其他表数据
SELECT * FROM employee;
显然用户1不能查询其他表数据
我们再针对用户2
首先查看其能不能查询table_locks_test表数据
我们发现执行语句后处于阻塞状态,然后我们释放用户1的写锁,注意观察用户2的变化
用户1解锁后,用户2从阻塞状态恢复并完成了查询操作
让用户1给table_locks_test加写锁,再看用户2能不能执行插入操作
我们发现用户2执行insert写操作被阻塞,我们让用户1释放写锁,看用户2变化
我们看到用户1释放锁的同时,用户2从阻塞状态恢复并完成了insert写操作
让用户1给table_locks_test加写锁,再看用户2能不能查询其他表数据
我们发现用户2可以查询其他表数据
总结:
现有A,B两个数据库连接,A连接给表加排他锁(写锁)时:
1. A连接可以查询该表,B连接查询该表进入阻塞,直到A连接释放锁,B连接恢复并执行查询该表操作。
2. A连接可以对该表进行写操作,B连接对该表写操作后进入阻塞状态,直到A连接释放锁,B连接恢复并执行对该表的写操作。
3. A连接不可以查询其他表,B连接可以查询其他表。
MyISAM引擎默认是write Lock优先于read Lock的,也就是说如果一堆写请求和一堆读请求同时要一张表的锁,那读请求只能在所有的写请求执行完成后才能获得执行机会。这也是MyISAM引擎不适用于以写为主的表,因为对表加写锁后,其他线程不能做任务操作,大量更新会使查询很难得到锁,从而造成永久阻塞。
二、行锁
偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁,多订力度小,发生所冲突的概率最低,并发也最高。
#查看行锁的状态:
SHOW STATUS LIKE 'innodb row_lock%';
对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁;对于普通SELECT语句,InnoDB不会加任何锁;当然我们也可以显示的加锁:
#共享锁
select * from 表名 where 条件 lock in share more;
#排他锁
select * from 表名 where 条件 for update;
InnoDB和MyISAM的最大不同点有两个:
- InnoDB支持事务(transaction);
- InnoDB默认采用行级锁。
2.1 事务相关
2.1.1 ACID属性
- 原子性(Automicity):
事务包含的所有操作要么全部成功,要么全部失败。 - 一致性(Consistency):
指事务必须使数据库从一个一致性状态变换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须处于一致性状态,即让数据保持一定的合理性。 - 隔离性(IsoIation):
是指当多个用户并发访问数据库时,比如同时操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。 - 持久性(Durability):
持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
2.1.2 并发事务处理带来的问题
- 更新丢失:
两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事物的存在,就会发生丢失更新问题,最后的更新覆盖了其他事务所做的更新。 - 脏读:
一个事务读到了另外一个事务已修改尚未提交的数据。 - 不可重复读:
针对update操作,一个事务操作范围内两个相同的查询返回了不同的数据,这就是不可重复读。
例:
员工发起事务1,查询工资为1W,此时事务1尚未关闭
财务发起事务2,给员工涨薪1000,并提交该事物
员工再次在事务1中查询工资发现变为1.1w - 幻读:
针对insert和delete,在同一个事务范围内,两次相同查询,后一次查询看到了前一次查询没有看到的行。
例:
助理通过事务1查询所有工资为1W的员工数为10人,事务尚未关闭
人事部此时发起事务2,将两位薪资1w的新员工录入系统,并提交事务。
此时助理在事务1中再次查出,发现有12人,多了两条记录。
2.1.3 事务的隔离级别
- Read uncommitted(读未提交):
就是一个事务可以读取到另一个事务未提交的数据 - Read committed(读已提交)
一个事务要等另外一个事务提交后才能读取数据 - Repeatable read(可重复读,mysql默认)
就是在开始读取数据(事务开启)时,不再允许修改操作 - Serializable(串行化)
在改级别下,事务串行化顺序执行,可以避免脏读,不可重复读,幻读。
查看事务的隔离级别可以使用如下语句:
#查看实物的隔离级别,全局和会话
SELECT @@global.tx_isolation,@@tx_isolation;
设置隔离级别:
#全局的,修改了全局的事务隔离级别,那么针对此后所有的会话有效,当前已经存在的会话不受影响
SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;
#当前会话,修改了会话的事务隔离级别,比如MyBatis,getSqlSession()的时候,只针对这一次拿到的Session有效;比如CMD命令行,只对这一次的窗口有效
SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别;
接下来我们开始漫长的测试:
新建表:
CREATE TABLE `row_locks_test` (
`id` int(4) NOT NULL AUTO_INCREMENT ,
`name` varchar(10) NOT NULL ,
`age` int(3) NOT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8;
我们用navicat连接数据库,模拟事务1操作
我们用命令提示符窗口连接相同的库,模拟事务2操作
先对事务1执行:
#当前会话 隔离级别设置为 读未提交
SET SESSION TRANSACTION ISOLATION LEVEL Read uncommitted;
查询当前会话隔离级别
#查看当前会话事务的隔离级别
SELECT @@tx_isolation;
事务2(黑窗口)执行如下sql,注意不提交
#开始一个事务
START TRANSACTION;
#执行插入操作
INSERT INTO row_locks_test(name,age) VALUES('李四',30);
#执行查询操作
SELECT * FROM row_locks_test;
我们在事务1(navicat)中执行如下sql:
#开始一个事务
START TRANSACTION;
#执行查询操作
SELECT * FROM row_locks_test;
我们发现事务2中尚未提交的操作,被事务1读取到了
提交事务1,2,继续测试:
#当前会话 隔离级别设置为 读已提交
SET SESSION TRANSACTION ISOLATION LEVEL Read committed;
查看当前会话隔离级别
#查看当前事务的隔离级别
SELECT @@tx_isolation;
让事物2(黑窗口)执行如下sql:
#开始一个事务
START TRANSACTION;
#执行插入操作
INSERT INTO row_locks_test(name,age) VALUES('王五',35);
#执行查询操作
SELECT * FROM row_locks_test;
让事物1(navicat)执行如下sql:
#开始一个事务
START TRANSACTION;
#执行查询操作
SELECT * FROM row_locks_test;
我们发现事务1并没有查询到事务2尚未提交的insert数据,避免了脏读。
此时提交事务2,让事务1再次查询
我们发现事务1查询到事务2已经未提交的insert数据,发生了幻读,不可重复读也不能避免(自行验证)。
提交事务1,继续测试:
#当前会话 隔离级别设置为 可重复读
SET SESSION TRANSACTION ISOLATION LEVEL Repeatable read;
查看当前会话隔离级别
#查看当前事务的隔离级别
SELECT @@tx_isolation;
让事物2(黑窗口)执行如下sql:
#开始一个事务
#开始一个事务
START TRANSACTION;
#执行更新操作
UPDATE row_locks_test SET age = 38 WHERE id = 3;
#执行查询操作
SELECT * FROM row_locks_test;
让事物1(navicat)执行如下sql:
#开始一个事务
START TRANSACTION;
#执行查询操作
SELECT * FROM row_locks_test;
我们发现事务1并没有读到事务2已经修改(update)但尚未提交的事务,Repeatable read隔离界别避免了脏读。
此时让事务1提交事务
#让事物1(navicat)执行如下sql:
#开始一个事务
START TRANSACTION;
#执行查询操作
SELECT * FROM row_locks_test;
我们发现事务1并没有读到事务2已经修改(update)并且提交的事务,Repeatable read隔离界别避免了不可重复读。
注意此处幻读可能会发生,因为不好测直接附上结论。
继续测试:
#当前会话 隔离级别设置为 读未提交
SET SESSION TRANSACTION ISOLATION LEVEL Serializable;
#查看当前事务的隔离级别
SELECT @@tx_isolation;
我们先看一下本次测试前的数据:
让事物2(黑窗口)执行如下sql:
#开始一个事务
START TRANSACTION;
#执行插入操作
INSERT INTO row_locks_test(name,age) VALUES('田七',50);
#执行删除操作
DELETE FROM row_locks_test WHERE id = 2;
#执行更新操作
UPDATE row_locks_test SET age = 66 WHERE id = 3;
#执行查询操作
SELECT * FROM row_locks_test;
让事物1(navicat)执行如下sql:
#开始一个事务
START TRANSACTION;
#执行查询操作
SELECT * FROM row_locks_test;
我们发现事务1并没有读到事务2已经插入(insert),删除(delete),修改(update)但尚未提交的事务,Serializable隔离级别避免了脏读。
提交事务2,让事务1再次查询该表数据
我们发现事务1并没有读到事务2已经插入(insert),删除(delete),修改(update)且已提交的事务,Serializable隔离级别避免了不可重复读,幻读。
总结:事务的隔离级别与存在问题的对应关系(是,存在)
事务的隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted | 是 | 是 | 是 |
Read committed | 否 | 是 | 是 |
Repeatable read | 否 | 否 | 是(可能) |
Serializable | 否 | 否 | 否 |
三、 索引失效行锁变表锁
首先要明确两点:
- 关于行锁,事务1对表中一条数据上锁,事务2同时对该表中另外一条数据做写操作,是不会有阻塞的。
- 关于表锁,事务1对表中一条数据上锁,事务2同时对该表中另外一条数据写操作,会被阻塞。
测试索引失效行锁变表锁:
原始数据如下:
#正确使用索引
EXPLAIN UPDATE row_locks_test SET `name` = '王八' WHERE age = 3;
#没有正确使用age索引,默认走了主键索引
EXPLAIN UPDATE row_locks_test SET `name` = '王八' WHERE age LIKE '%6';
首先让navicat执行,但不提交:
#开始一个事务
START TRANSACTION;
#对id=3数据执行更新
UPDATE row_locks_test SET `name` = '王八' WHERE age LIKE '%6';
然后让黑窗口执行对id=4,id=5的数据执行update:
#执行对id=4,id=5的数据执行update
UPDATE row_locks_test SET `name` = '王八' WHERE age LIKE '%0';
我们观察黑窗口发现该条update操作被阻塞:
我们提交navicat的事务,注意观察黑窗口:
navicat的事务提交瞬间,黑窗口更新操作也执行成功。
由上述测试得出:索引失效,行锁变表锁
四、 间隙锁
准备数据,原始数据如下
UPDATE row_locks_test SET `name` = '间隙锁' WHERE id > 3 AND id < 6;
间隙锁顾名思义会对区间范围内的数据上锁