Mysql之数据库锁（表锁和行锁）详解

1. 什么是锁？

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

1. 在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种共享资源，如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题。
2. 锁冲突是影响数据库并发访问性能的一个重要因素，从这个角度来说，锁对数据库而言显得尤为重要，而且也更加复杂

生活举例：

2. 锁的分类

2.1 从对数据的操作类型分为：读锁（共享锁）和 写锁（排他锁）

读锁：针对同一份数据，对该数据的读操作可以同时进行且不受影响。

写锁：写操作未完成前，会阻断其他的读操作和写操作。

2.2 从对数据的操作粒度分为：表锁 和 行锁

表锁

表锁特点

• MylSAM引擎使用表锁，开销小，加锁快，无死锁，锁定力度大，发生锁冲突的概率最高。
• 并发度最低
• 不支持事务

3.建立数据

create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

select * from mylock;

查看数据库锁

SHOW OPEN TABLES in hanyxx; --查看数据库hanyxx中的表是否加锁
LOCK TABLE book read , phone write -- book表加读锁，phone表加写锁
--全部解锁
UNLOCK TABLES;

3.1 手动加锁

释放锁

3.1加读锁

.

加表锁
读当前表，可以
改当前表，不可以
读其他表：不可以

表锁（读锁）
session_1给表加上表锁（读锁）

session_1和其他主机都可以读取该表的信息
session_1不能读取其他表的信息，但其他session可以读取库中其他表的信息
session_1不能对锁住的表进行修改
其他session对表进行修改，会被阻塞，直到表锁（读锁）被释放

此时session_2来对表修改，会被阻塞

总结：

读锁不会阻塞读，只会阻塞写。
写锁会阻塞读和写

3.2 写锁：排他锁

3.3行锁（重中之重）

行锁特点

1. 开销大，枷锁慢，会出现死锁
2. 锁定粒度最小，发生锁冲突概率最低，并发度最高

InnoDB和MyISAM最大的不同点

1. 支持事务
2. 采用行锁和外键

学习表锁之前先复习一下事务

1.事务具有ACID的属性


更新丢失

脏读

不可重复读

幻读

事务隔离

扩展：事务的可序列化和实体的可序列化不是一个概念。
实体加序列化是为了可以直接在流中传输实体。

查看事务隔离

show variables like 'tx_isolation'; -- MySQL 5.7之前的版本
show variables like 'transaction_isolation'; -- MySQL 5.7之后的版本

建立表

-- 创建表
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;
-- 插入数据
INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(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);
-- InnnDB事务自动提交，如果需要演示行锁，需要关闭自动提交
SET autocommit=0;

可重复读，一个事务内，多次读取同一数据是一致的，需要提交当前事务，执行下一次事务。

结论：

如果两个客户端对同一条记录进行修改

1. 客户端A修改后，未提交（未commit），此时客户端B修改，则会阻塞
2. 客户端A修改后，提交后，客户端B再修改，则不会阻塞
3. 如果两个客户端分别对不同的记录行进行修改，则不会被阻塞

3.4索引失效

索引失效，行锁变表锁（通过varchar类型不加单引号让索引失效）

当索引失效后，即使多个客户端操作的不是同一条记录，如果未提交，其他客户端也会进入阻塞状态，所以要避免索引失效

为甚么索引失效行锁会变表锁

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

间隙锁危害

间隙锁好处：间隙锁完全解决了幻读。

3.6面试题
3.6.1 如何锁定一行？？

优化建议：

优化建议

1. 尽可能让数据检索通过索引完成，避免无索引，让行锁升级为表锁
2. 合理设计索引，缩小锁的范围
3. 尽可能减少检索条件，避免间隙锁
4. 尽可能控制事务的大小，减少锁定资源量和时间长度
5. 尽可能采用低级别的事务隔离级别

总结：

3.7 什么是页锁？

页锁（补充）：

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