数据库锁

数据库优化 学习笔记

        数据库的锁大致有三类，表锁、行锁、间隙所。锁是计算机协调多个进程或线程并发访问某一资源的机制。

一、表锁（适合用来“读”）

        特点： 偏向 MyISAM 存储引擎，开销小，加锁快，无死锁，锁定粒度大，发送锁冲突的概率最高，并发度低

1.1、表锁案例

• 建表

	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;					-- 添加读锁
	lock table 表名 write;					-- 添加写锁
	lock table 表名 read, 表名2 read;			-- 给多个表添加读锁

	lock table mylock read;

	-- 查看所有的表, 可看看是否添加成功
	show open tables;

• 释放表锁

	unlock tables;

1.2、测试

        开两个窗口模仿两个客户端。

• 在其中一个添加 读锁（这里在左边添加，关闭该窗口会自动释放锁，相当于断开连接）
  
  
  
  
  

• 添加 读锁
  
  
  

1.3、总结

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

• 对 MyISAM 表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进行的写操作。

• 对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。

• 读锁 会阻塞其他进程 “写操作”，但是不会阻塞 “读操作”。写锁 会阻塞其他经常的 “读操作” 和 “写操作”。

• 所有，表级锁比较适合用来 “读”。
  
  

1.4、show status like “table%”;

	-- 执行这个命令会看到几个参数
	-- 前两个是 " Table_locks_immediate " , " Table_locks_waited "
	show status like "table%";	

• Table_locks_immediate： 表示产生表级锁定的次数，每获得一个锁就加1。

• Table_locks_waited： 表示发生表级锁争抢时，所等待的次数。若该值很大，说明当前存在比较严重的表级锁的竞争。
  
  

---

二、行锁（适合用来“写”）

        特点： 偏向 InnoDB 存储引擎，开销大，加锁慢，会出现死锁。锁定粒度最小，发生锁冲突的概率最低（因为可以锁定不同的行），并发度也最高。

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

2.1、表锁案例

• 建表

	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');
	
	create index idx_test_innodb_a on test_innodb_lock(a);
	
	create index idx_test_innodb_b on test_innodb_lock(b);

• 关闭自动提交（对行操作一般都涉及提交）

	set autocommit=0;			-- 两个窗口都要关闭自动提交

2.2、如何分析行锁定

• 查看

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

---

三、间隙锁

3.1、什么是间隙锁

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

        innodb 也会对这个 “间隙” 加锁，这种锁机制就是所谓的 间隙锁。

3.2、间隙锁的危害

        因为 SQL 执行过程中通过范围查找的话，他会锁定整个范围内所有的索引值，即使这个键值并不存在。

        间隙锁有一个比较致命的弱点, 就是当锁定以为范围键值之后, 即使某些不存在的键值也会被无辜的锁定, 而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

	目前的理解是, 如果左边的范围有缺失值, 即使右边插入的数字不在该范围内, 也会阻塞。

3.3、如何锁定一行

	select * from test_innodb_lock where a = 8 for update;