MySQL锁机制一(表锁)

330 阅读2分钟

这是我参与8月更文挑战的第7天,活动详情查看:8月更文挑战

1、表锁(偏读)

偏向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');

锁的基本操作

# 查看哪些表有锁
SHOW OPEN TABLES;

# 为mylock表加读锁,为book表加写锁
LOCK TABLE mylock READ,book WRITE;

# 解锁
UNLOCK TABLES;

加读锁后的效果

# 给mylock加读锁
LOCK TABLE mylock READ;

SELECT * FROM mylock;

UPDATE mylock SET `name`="a2" WHERE id = 1; 

SELECT * FROM book;

UNLOCK TABLES;

结果:

查询当前加锁表可以。

更新当前加锁表失败。

查询其他没有加锁的表失败。

这时再启动一个终端对其加锁的表操作,可以查询,修改操作会被阻塞。

释放锁,另一个终端获得锁,执行成功。

加写锁后的效果

LOCK TABLE mylock WRITE;

SELECT * FROM mylock;

UPDATE mylock SET `name`="a2" WHERE id = 1; 

SELECT * FROM book;

结果:

可以查询当前表。

可以修改当前表。

无法查询其他没有加锁的表。

其他终端无法查询当前加锁的表,任何操作都会被阻塞。

释放锁,其他终端获得锁,执行成功。

总结:就是读锁会阻塞写操作,但是不会阻塞读操作,而写锁会阻塞读和写操作。

2、表锁分析

使用 SHOW STATUS LIKE "table%"; 进行表锁分析

SHOW STATUS LIKE "table%"; 的两个参数

  • Table_locks_immediate:产生表级锁的次数,表示可以立即获取锁的查询次数,每次立即获取锁值加1。
  • Table_locks_waited:出现表级锁定争用而发生等待的次数,不能立即获取锁的次数,每一次等待锁值加1。

MyISAM的读写锁调度是写优先,所以MyISAM引擎不适合做主表的引擎,因为写锁后,其他线程不能做任何操作,大量的查询很难得到锁,从而造成一直的堵塞。