Skip to main content

MarshioAbout 4 minMySQL

数据库的锁经常与事务一起讨论,因为锁只有在多并发的情况下才会起作用。

MySQL中锁又与数据库引擎有关,InnoDB存储引擎支持行级锁,表级锁,MyISAM和MEMORY存储引擎支持表级锁。BDB存储引擎支持页面锁。也支持表级锁。

MySQL的行锁又分为共享锁(S锁),排他锁(X锁)。

MySQL三种锁的特性

开销死锁粒度并发性能
表级锁大,加锁快不会发生死锁发生冲突的概率高,并发性低
行级锁小,加锁慢会发生死锁发生冲突的概率低,并发性高
页面锁会发生死锁发生冲突的概率中,并发性一般

MyISAM

MyISAM只支持表级锁,且不支持事务,所以用的很少。

InnoDB

InnoDB即支持表级锁,也支持行级锁,默认是行级锁。

共享锁

可以共享

# 会造成加S锁的情况
SELECT <columns> from <table_name> LOCK IN SHARE MODE;
image
image

排他锁

不可共享

# 会造成加X锁的情况
# FOR UPDATE指当前读,即读取最新数据
SELECT <columns> from <table_name> FOR UPDATE;

# 插入操作
INSERT INTO <table_name> values(columns_value);

# 更新操作
UPDATE <table_name> SET (column=new_value,column=new_value...);

# 删除操作
DELETE FROM <table_name> WHERE column=value;
FOR UPDATE
image
image
新增
更新
image
image
删除

行锁实现

InnoDB行锁是通过给索引上的索引项加锁来实现的。这一点与Oracle不同,Oracle是通过在数据块中对相应的数据行加锁来实现。

InnoDB这种加锁意味着只有通过索引条件检索数据,InnoDB才能使用行锁,否则InnoDB将使用表锁。

# 查看默认事务隔离级别
SELECT @@TRANSACTION_ISOLATION;

# 查看表索引
SHOW INDEX FROM <table_name>;

# 开启事务
BEGIN# 事务A FOR UPDATE会给ID=1的查询加锁,因为是当前读,此时应该是给ID=1的数据加行锁
SELECT * FROM <table_name> WHERE ID = 1 FOR UPDATE;

# 事务B 尝试给当前读,即获取读锁,被阻塞,如果事务A加了行锁,此时需要等待是正常的
SELECT * FROM <table_name> WHERE ID = 1 FOR UPDATE;

# 事务B 尝试给当前读,即获取读锁,被阻塞,如果事务A加了行锁,此时不需要等待,但实际上是等待,所以判断是表锁
SELECT * FROM <table_name> WHERE ID = 2 FOR UPDATE;
image
image
# 接下来我们查看一下,加锁的锁信息
# 在事务B尝试加锁后,我们查询当前数据库的INNODB_LOCKS,即正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

# 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
image
image

INFORMATION_SCHEMA.INNODB_LOCK_WAITS

  • requesting_trx_id:请求锁的事务ID
  • requesting_trx_id:请求的锁的ID
  • blocking_trx_id:阻塞的事务的ID
  • blocking_lock_id:阻塞的锁的ID

INFORMATION_SCHEMA.INNODB_LOCKS

  • lock_id:锁ID

  • lock_trx_id:事务ID

  • lock_mode:锁模式

    • S:S锁,即共享锁(Share)
    • X:X锁,即排他锁(Exclusive)
  • lock_type:锁类型

    • RECORD:记录锁,锁的是索引,如果表上没有索引,那么 InnoDB会自动创建一个隐藏的聚簇索引,此时锁住的就是这个隐藏的聚簇索引。所以当一个 SQL语句没走索引,或该表没有索引时,将会在每个自动创建出来的聚簇索引上加X锁,间接的实现了表级锁,即锁升级 (?)。
  • lock_tabe:锁定的表

  • lock_index:锁定的索引,因为 InnoDB的锁需要命中索引,然后给索引加锁

    • GEN_CLUST_INDE:聚类索引,这个索引是由于表没有建立索引,所以在进行加锁时,需要自动建立一个索引,但是这样建立的索引会给所有的行加锁,导致行锁升级成了表锁。所以当你的 lock_index = GEN_CLUST_INDE时,你需要查看表是否缺少索引,及时建立索引,避免表锁。
  • lock_space:锁定的空间

  • lock_page:锁定的页,这里涉及到了内存

  • lock_rec

  • lock_data:锁定的数据

查看锁

# chakan锁执行次数
SHOW STATUS LIKE '%lock%';
image
image
  • Table_locks_waited:如果该值较大,说明存在较严重的表级锁竞争

Table_locks_immediate表示立即释放表锁数,Table_locks_waited表示需要等待的表锁数, 如果 Table_locks_immediate / Table_locks_waited > 5000,最好采用 InnoDB引擎, 因为 InnoDB是行锁而 MyISAM是表锁,对于高并发写入的应用 InnoDB效果会好些。

不同隔离级别涉及到的锁 参考: