数据库锁机制

目录

1、表锁和行锁

2、间隙锁(Gap Lock)和临键锁(Next-key Locks)

3、锁机制分析

---

数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致我们说的脏写、脏读、不可重复读、幻读这些问题。这些问题的本质都是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。

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

在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

1、表锁和行锁

（1）从性能上分为乐观锁(用版本对比来实现)和悲观锁

（2）从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)

读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁

（3）从对数据操作的粒度分，分为表锁和行锁

表锁：每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。

行锁：每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

表锁案例分析

--建表SQL
CREATE TABLE `mylock` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`NAME` VARCHAR (20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

--插入数据
INSERT INTO mylock (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO mylock (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO mylock (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO mylock (`id`, `NAME`) VALUES ('4', 'd');

操作命令

--手动增加表锁
lock table 表名称 read(write),表名称2 read(write);

--查看表上加过的锁
show open tables;

--删除表锁
unlock tables;

加读锁：当前session和其他session都可以读该表，当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待。

加写锁：当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞。

案例结论

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

2、对MylSAM表的写操作(加写锁) ，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

行锁案例分析

InnoDB与MYISAM的最大不同有两点：

（1）InnoDB支持事务（TRANSACTION），（2）InnoDB支持行级锁

--建表
CREATE TABLE `account` (
  `id` INT (11) NOT NULL AUTO_INCREMENT,
	`name` varchar(255) DEFAULT NULL,
	`balance` int(11) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--插入数据
INSERT INTO `account` (`name`, `balance`) VALUES ('mike', '500');
INSERT INTO `account` (`name`, `balance`) VALUES ('bob', '500');
INSERT INTO `account` (`name`, `balance`) VALUES ('lucy', '500');

行锁效果：一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞

总结：

MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会自动给涉及的表加写锁。

InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。

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

2、间隙锁(Gap Lock)和临键锁(Next-key Locks)

间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read(可重复读)，间隙锁在某些情况下可以解决幻读问题。

如果account表里数据如下：

那么间隙就有 id 为 (3,7)，(7,16)，(16,正无穷) 这三个区间。

范围修改：如果在Session_1下面执行

--范围修改
update account set name = 'xiaoming' where id > 5 and id < 11;

那么其他Session没法新增或修改id在(3,16]区间的任何一行数据，注意最后那个16也是包含在内的。

间隙锁是在可重复读隔离级别下才会生效。

临键锁(Next-key Locks)

Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。

3、锁机制分析

（1）无索引行锁会升级为表锁(RR级别会升级为表锁，RC级别不会升级为表锁)

锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

锁定某一行还可以用 lock in share mode (共享锁) 和 for update(排它锁)，例如：

select * from account where id = 1 for update;

结论：Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。

（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）查看INFORMATION_SCHEMA系统库锁相关数据表

-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;

-- 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;

-- 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id

-- 查看锁等待详细信息
show engine innodb status\G; 

（4）锁优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少检索条件范围，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
• 尽可能低级别事务隔离