MySQL数据库--锁机制

锁机制

锁的分类

从锁的操作类型分

读锁(共享锁)

​ 针对于同一份数据，多个读操作可以同时进行而不会互相影响

写锁(排它锁)

​ 当前写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分

表锁

偏向于Myisam存储引擎，开销小，加锁快，无思索，锁定粒度大

建表sql

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,表名字2 write;

查看锁:show open tables;

释放锁:unlock tables;

加了读锁后:多个连接可以同时读取，但是不可修改，其他表想修改该表会处于阻塞状态。锁表的连接不能读取其他表。

加了写锁后:锁表的连接，可以访问和修改被锁的表，但是不能读取其他表。其他表若想查询该表，会处于阻塞状态。若没发生阻塞就是该表从缓存中取得的数据。

简而言之：就是读表会阻塞写，但不会阻塞读，而写锁则会把写和读都堵塞

myisam 执行查询语句之前，会自动给涉及的所有表加读锁，在执行增删改操作之前，会自动给涉及的表加写锁，mysql的表级锁有两种模式:

表共享读锁

表独占写锁

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，没立即获取值加一

Table_locks_waited：出现表级锁定征用而发生等待的次数（不能立即获取锁的次数，每次加一），此值高则说明存在较严重的表级锁征用情况。

MyIsam的读写锁调度是写优先，这也是它不适合做写为主表的引擎，因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而操作永久阻塞

行锁

偏向于innodb引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度最高。和myisam最大的不同：支持事务，采用行级锁

建表sql

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(2,'b3');
insert into test_innodb_lock values(3,'b5');
insert into test_innodb_lock values(4,'b58');
insert into test_innodb_lock values(5,'b45');

set autocommit=0;开启事务

如事务一对一行进行修改，事务二也对该行修改会阻塞。直到事务一提交。

索引失效导致行锁变表锁

若varchar类型不写单引号，导致索引失效会导致行锁变表锁，从而阻塞其他表对其他行的修改。在两表都提交后，索引重新排列后才ok

间隙锁

当使用范围条件时，innodb会给所有符合条件的已有数据记录的索引项加锁，对于键值在条件范围内并不存在的值称间隙，同时也加锁。

间隙锁对不存在的值也加锁，影响了插入效率，可能对性能造成极大危害。

面试题：如何锁定一行

begin;

select * from test_innodb_lock where a=8 for update

commit;

for update:锁定某一行，其他的操作会被阻塞，直到锁定行commit；

改了配置参数，应当重启重连

总结

innodb存储引擎实现了行级锁定，锁定机制带来的性能损耗更强一点，但是整体并发处理能力要更强，当系统并发量该是，就有明显的优势了。当使用不当时，有可能更差。

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:总等待次数 ！

尽可能的让所有数据减速都通过所有来完成，避免无索引升级为表锁

合理设计索引，尽量缩小锁的范围

尽可能的减少检索条件，避免间隙锁

尽量控制事务大小，减少锁定资源量和时间长度

尽可能江都事务隔离级别

页锁

锁定粒度，开销和加锁时间介于两者之间，会有死锁，并发一般。