发布时间:2024-01-18 11:30
CPU、RAM、I/O
等)的争用以外,数据也是一种供许多用户共享的资源。如何保证资源并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。1. 从对数据操作的类型(读/写分)
2. 从对数据操作的粒度分
1. 建表和插入数据的sql语句
create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) 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');
select * from mylock;
2. 手动在表上增加读锁
-- 加锁的sql语句
lock tables 表名字 read(write),表名字2 read(write),其它;
-- 示例(在表上添加读锁)
lock table mylock read
-- 查看表上加过的锁
show open tables;
-- 对表上加过锁的进行解锁
unlock tables;
3. session_1和session_2操作对比
1. session_1
# 对mylock表上读锁
lock table mylock read;
# 1. 对自己锁住的表的读操作
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
# 2. 对其它表的读操作
mysql> select * from tbl_emp;
ERROR 1100 (HY000): Table 'tbl_emp' was not locked with LOCK TABLES
# 3. 对自己锁住的表的写操作
mysql> update mylock set name = 'a1' where id = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
- 当前session可以查询该表记录。
- 当前session不能查询其它没有锁定的表。
- 当前session插入或者更新锁定的表都会提示错误。
2. session_2
#session_2并未对mylock上锁
#1. 其对mylock表的读操作
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
#2. 其对非mylock的其他表的读操作
mysql> select * from tbl_emp;
+----+------+--------+
| id | name | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
| 8 | s9 | 51 |
+----+------+--------+
8 rows in set (0.01 sec)
#3. 其对mylock表的写操作
mysql> update mylock set name = 'a1' where id = 1;
#此时其会阻塞在这里,只有当session_1对其进行解锁后
mysql> update mylock set name = 'a1' where id = 1;
Query OK, 1 row affected (10.76 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 其它session可以查询该表记录。
- 其它session可以查询或者更新其它没有锁定的表。
- 其它session插入或者更新锁定的表都会一直等待获得锁(就是被阻塞了)。
session_1和session_2操作对比
1. session_1
# 其对mylock加写锁
lock table mylock write;
# 1. 其对自己锁定的表进行查询操作
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a1 |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
# 2. 其对自己锁定的表进行更新操作
mysql> update mylock set name = 'a' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 3. 其对其它未锁定的表的查询操作
mysql> select * from tbl_emp;
ERROR 1100 (HY000): Table 'tbl_emp' was not locked with LOCK TABLES
2. session_2
# 其对session_1加了写锁的表进行查询和更新还有插入操作都会被阻塞
mysql> select * from mylock;
# 上面的sql语句会被阻塞在这里,当session_1进行解锁之后,其查询操作才能被完成。
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (10.21 sec)
#由时间可以看出这里被阻塞了好久。
MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。
MySQL的表级锁有两种模式
锁类型 | 可否兼容 | 读锁 | 写锁 |
---|---|---|---|
读锁 | 是 | 是 | 否 |
写锁 | 是 | 否 | 否 |
结论
所以对MyISAM表进行操作,会有以下情况:
1. 看看哪些表被加锁了
show open tables;
2. 如何分析表锁定
# 展示相关状态变量的SQL语句
show status like 'table%';
# 加写锁之后的状态变量的分析
mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 247 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 15 |
| Table_open_cache_misses | 4 |
| Table_open_cache_overflows | 0 |
+----------------------------+-------+
5 rows in set (0.00 sec)
此值高说明存在较严重的表级锁争用情况
。MyISAM的读写锁调度是写优先,这也是MyISAM不适合做写为主表的引擎
。因为写锁后,其他线程不能做任何操作,大量的更新会使查询很难得到锁,从而造成永远阻塞。1. 特点
2. 事务复习
事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称事务的ACID属性。
脏读、不可重复读和幻读,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。
Read uncommitted(读未提交)
:如果一个事务已经开始写数据,则另外一个事务不允许同时进行写操作,但允许其他事务读此行数据。Read uncommitted(读未提交)
:如果一个事务已经开始写数据,则另外一个事务不允许同时进行写操作,但允许其他事务读此行数据。Repeatable read(可重复读取)
:可重复读取是指在一个事务内,多次读同一个数据。Serializable(可序化)
:提供严格的事务隔离,它要求事务序列化执行,事务只能一个接着一个地执行,但不能并发执行,如果仅仅通过“行级锁”是无法实现序列化的,必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。序列化是最高的事务隔离级别,同时代价也是最高的,性能很低,一般很少使用。读数据一致性及允许的并发副作用 | 读数据一致性 | 脏读 | 不可重复度 | 幻读 |
---|---|---|---|---|
未提交读 | 最低级别,只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
已提交读 | 语句级 | 否 | 是 | 是 |
可重复读 | 事务级 | 否 | 否 | 是 |
可序列化 | 最高级别,事务级 | 否 | 否 | 否 |
show variables like 'tx_isolation' -- 查看当前数据库的事务隔离级别
1. 建表和插入数据以及建索引的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(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
SET autocommit=0; # 关闭事务的自动提交
2. session_1和session_2的操作对比
session_1修改数据后session_2查看
# 自动提交关闭之后
# session_1
mysql> update test_innodb_lock set b = '4001' where a = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4001 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
# session_2
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
# session_2在session_1修改数据之后是无法查看到数据的变化,但session_1自己可以看到。
# 只有当双方都commit之后session_2才可以看到。
# 这并不是什么误操作,是因为自动提交被关闭了。
session_1和session_2修改同一行数据
# session_1
mysql> update test_innodb_lock set b = '4002' where a = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# session_2
mysql> update test_innodb_lock set b = '4003' where a = 4;
# 此时session_2会陷入阻塞,当session_1提交事务之后,session_2才能修改成功。
#并且session_2提交事务之后,它的修改才能生效。
#查询出来的结果
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4003 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
session_1和session_2修改不同行数据
# session_1
# 修改第九行数据
mysql> update test_innodb_lock set b = '9001' where a = 9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# session_2
# 修改第四行数据
mysql> update test_innodb_lock set b = '4003' where a = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 当双方多次提交事务之后的查询结果,双方互不影响。
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4003 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9001 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
# session_1
# 我们都知道varchar类型的数据在使用的时候不加单引号会导致索引失效 b字段的类型是varchar
mysql> update test_innodb_lock set a = 9 where b = 9001;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# session_2
mysql> update test_innodb_lock set b = '4001' where a = 4;
# session_2会被上述语句阻塞,当session_1进行提交之后它才会执行自己的语句
# session_2也commit之后的执行结果
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4001 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9001 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
1. 什么是间隙锁?
当我们使用范围条件而不是相等条件检索数据,并请求共享或排它锁时,InnoDB会给符合条件的已有数据记录的索引加锁;对于键值在条件范围但并不存在的记录——间隙,InnoDB也会对这个间隙加锁,这种锁机制就是所谓的间隙锁。
2. 间隙锁的危害
因为Query执行过程中通过范围查找的话,他会锁定整个范围内所有的索引键值,即使这个键值不存在。间隙锁有一个致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被锁定,而造成锁定的时候无法插入锁定范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
3. 案例演示
#session_1
mysql> update test_innodb_lock set b = '0629' where a > 1 and a < 6;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
# 他这是范围更新,但是a = 2不存在,所以我们使用session_2进行插入。
# session_2
mysql> insert into test_innodb_lock values(2,'2000');
#session_2会阻塞在这里
# 当session_1进行commit操作之后session_2才会执行自己的sql语句
# 当session_2也进行commit之后的查询结果
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 0629 |
| 4 | 0629 |
| 5 | 0629 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9001 |
| 1 | b1 |
| 2 | 2000 |
+------+------+
10 rows in set (0.00 sec)
select xxx... for update
锁定某一行后,其它的操作会被阻塞,直到锁定行的会话提交commit
。
** 案例演示**
# session_1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_innodb_lock where a = 8 for update;
+------+------+
| a | b |
+------+------+
| 8 | 8000 |
+------+------+
1 row in set (0.00 sec)
# 上述sql语句对 a = 8 的这一行进行了锁定
# session_2
mysql> update test_innodb_lock set b = '8001' where a = 8;
# 此时session_2会被阻塞,直到session_1进行了commit操作
# 双方都进行了commit操作之后的查询结果
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 0629 |
| 4 | 0629 |
| 5 | 0629 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8001 |
| 9 | 9001 |
| 1 | b1 |
| 2 | 2000 |
+------+------+
10 rows in set (0.00 sec)
InnoDB
存储引擎由于实现了行级锁定,虽然在锁定机制实现方面所带来的性能损耗比表级锁更多,但是在整体并发处理能力方面要远优于 MyISAM
的表级锁定的。当系统并发量比较高的时候,InnoDB
的整体性能和 MyISAM
相比会有比较明显的优势。InnoDB
的行级锁同样也存在问题,当我们不当使用时,可能会使 InnoDB
的整体性能表现比 MyISAM
更差。1. 怎样分析
InnoDB_row_lock
状态变量来分析系统上的行锁争夺情况show status like '%innodb_row_lock%'
# 展示结果
mysql> show status like '%innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 143630 |
| Innodb_row_lock_time_avg | 23938 |
| Innodb_row_lock_time_max | 50403 |
| Innodb_row_lock_waits | 6 |
+-------------------------------+--------+
5 rows in set (0.05 sec)
状态变量说明:
五个状态变量中比较重要的:
尤其 是当前等待次数很高,而且每次等待时长也很长的时候,需要分析系统中出现多次等待的原因,并根据分析结果指定优化计划。
2. 优化建议
尽可能让所有数据检索都通过索引完成,避免无索引行锁升级为表锁;
合理设计索引,尽量缩小锁的范围;
尽可能减少检索条件,避免间隙锁;
尽量控制事务大小,减少锁定资源量和时间长度;
尽可能使用低级别的事务隔离;
页锁的开销和加锁时间介于表锁和行锁之间,会出现死锁,锁定粒度介于表锁和行锁之间,并发度一般。了解一下即可。