数据库锁

锁是计算机协调多个进程或线程并发访问某一资源的机制
数据库锁分为以下几种

1. 表锁
2. 行锁
3. 间隙锁

表锁(偏读)

偏向MyISAM存储引擎,开销小,加锁快;无死锁,锁定粒度大,发送锁冲突的概率最高,并发度低
表锁分为读锁和写锁两种。

表锁案例

建表并插入数据

create table mylock(
id int not null primary key auto_increment,
name varchar(20)
)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');

手动增加表锁

lock table 表名字 read(write),表名字2 read(write);--read是读锁，write是写锁;

当我们创建读锁时，使用不同的MySQL端口是不影响查询该表的操作

但是创建锁的窗口查询其他表时则会报错

其他窗口读取其他表则不会有影响

但是使用改的操作时。
在创建锁的窗口会直接报错，改有锁的表和其他表皆是如此


在另一个窗口改有锁的表则会堵塞

当创建锁的窗口解锁后，则会解除堵塞

unlock tables;

而改其他的表则不会有影响

当创建写锁的表进行查询的操作时
创建锁的窗口读取有锁的表不会有影响

读取别的表则会报错

别的窗口读取有锁的表则会堵塞

当解锁时则会解堵塞

读取其他表则不会有影响

而进行写操作时
写入有锁的表原窗口不会有影响

写入其他表则会报错

其他窗口写入有锁的表则会堵塞

解锁时则会解堵塞

写其他表则不会有影响

表锁总结

MyISAM在执行查询语句(select)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁

1. 对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其他进行的写操作
2. 对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进程的读写操作

行锁(偏写)

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

InnoDB与MyISAM的最大不同点,支持事务,采用了行级锁

行锁案例

建表

create table test_lock(a int(11),b varchar(16))engine=innodb;
insert into test_lock values(1,'b2');
insert into test_lock values(3,'3');
insert into test_lock values(4,'4000');
insert into test_lock values(5,'5000');
create index idx_a on test_lock(a);
create index idx_b on test_lock(b);

首先我们先将MySQL的自动提交关闭

set autocommit = 0;

此时我们在一个窗口中执行修改操作，并查看发现在该表中显示已经生效。

但是在另一个窗口中查看却还没修改过来

这时我们在修改的窗口commit提交一下再查看发现另一个窗口中还是没改过来

这是因为我们还需在这个窗口也commit提交以下，这样便也修改过来了。

当我们在两个窗口中同时对同个数据进行修改时
在第一个执行的窗口不会有影响

而后一个执行的则会堵塞

当另一边commit提交后则会解堵塞。而这个修改要成功也需要commit提交后。
若过长时间没有解堵塞则会报错

如何分析行锁定

通过检查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 系统启动后到现在总共等待的次数

间隙锁

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,innodb会给符合条件的已有数据记录的索引项加锁, 对于键值在条件范围内但并不存在的记录,叫做"间隙"

innodb也会对这个"间隙"加锁,这种锁机制就是所谓的间隙锁
当我们在一个窗口执行下面语句时

在另外一个窗口执行插入一个a=2的数据则会堵塞，尽管此时该表中没有a=2的数据

但执行修改命令的表commit提交后才会解堵塞

间隙锁的危害

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

如何锁定一行

select * from test_innodb_lock where a = 8 for update;