MYSQL(九)

mysql学习笔记整理-锁机制

锁机制

因为 Mysql 支持多线程方式，所以可以同时处理多个客户端请求。有时为了防止客户端同时修改数据，我们使用锁操作完成。

比如一个用户在修改数据，另一个用户也要修改该条数据，我们可以让第一个用户独占这个表记录，等他操作完再让第二个用户操作。

下面是典型的商城应用，多用户购买商品时可以使用锁机制保障库存的准确性。

储存引擎

InnoDB 是主流储存引擎并支持行级锁的，有更高的并发处理性能，下面来演示行锁的运行过程。MyIsam引擎在最新版本的 MYSQL 中已经废弃所以不过多讨论了。

• 行锁开销大，锁表慢
• 行锁高并发下可并行处理，性能更高
• 行锁是针对索引加的锁，在通过索引检索时才会应用行锁，否则使用表锁
• 在事务执行过程中，随时都可以执行锁定，锁在执行 COMMIT 或者 ROLLBACK 的时候释放

事务处理

1.A 事务执行以下代码但不提交

BEGIN;
UPDATE stu SET sname = 'hdcms' WHERE id=1;

2.B 事务执行以下代码，可以正常执行

BEGIN;
update stu set sname = '后盾人' where id=3
COMMIT;

3.但 B 事务更新与 A 事务相同的记录则无法操作，执行过程发生阻塞

BEGIN;
UPDATE stu SET sname = 'hdcms' WHERE id=1;
...

4.当 A 执行执行COMMIT 提交后，解锁记录行这时 B 事务继续执行

...
COMMIT;
...

非索引阻塞

使用非索引字段筛选时，将造成全表锁定即表级锁，应该避免这种情况发生，提升数据库的并发性能。
1.事务 A 执行以下代码，因为sname字段没有添加索引，造成锁定整个表

BEGIN;
UPDATE stu SET sname = 'hdcms' WHERE sname ='后盾人';

2.现在事务 B 更新任何一条记录都会造成阻塞，因为现在是表锁状态

BEGIN;
update stu set sname = '小明' where id=1
-- 阻塞中...

3.将 sname字段添加索引后，行锁功能就又有效了

范围锁

查询没有指定明确范围时也会造成大量记录的锁定
1.事务 A 筛选时使用了范围区间，将会造成表锁

BEGIN;
UPDATE goods SET num=200 WHERE id>1 AND id<3;

2.事务 B 将不能修改表中的 ID 大于 2 的记录

BEGIN;
update goods set num =1 where id=2;
-- 阻塞中...

但可以更改 ID 为 1 的记录

update goods set num =1 where id=1;

3.执行添加时因为不在 id 为 1~3 的范围内所以可以添加，但如果添加时指定 ID 为 2 将会阻塞。

insert into goods (name,num) values('西瓜',200);

悲观锁

非观锁指对数据被外界修改持保守态度，在整个数据处理过程中，将数据处于锁定状态，可以很好地解决并发事务的更新丢失问题。

下面演示商城下单情况，要用户购买商品后我们要减少库存，如果在高并发情况下多个用户同时修改库存表，会造成库存数据异常，使用悲观锁可以解决这个问题。
1.事务 A 执行悲观锁操作后，其他事务执行同一代码时将阻塞

BEGIN;
SELECT * FROM goods WHERE id=1 FOR UPDATE;
UPDATE goods SET num=num-2 WHERE id=1;
...

2.事务 B 执行以下代码将不能查询库存，必须等事务 A 提交或回滚事务

BEGIN;
## B事务中查询中也要使用 FOR UPDATE 悲观锁
SELECT * FROM goods WHERE id=1 FOR UPDATE;
-- 阻塞中...

3.事务 A 提交后，事务 B 会得到事务 A 操作后的结果

...
COMMIT;
...

乐观锁

在每次去拿数据的时候认为别人不会修改，不对数据上锁，但是在提交更新的时候会判断在此期间数据是否被更改，如果被更改则提交失败。

下面使用版本字段来实现乐观锁操作，并实现更改商品库存的案例。
1.事务 A 查询商品库存，获取了商品记录，记录中有 VERSION 字段用于记录版本号（目前为 0）

BEGIN;
SELECT * FROM goods WHERE id = 1;

2.事务 B 同时查询，也获取了版本号为 0 的记录

BEGIN;
SELECT * FROM goods WHERE id = 1;

3.事务 A 更改库存，并增加版本号

UPDATE goods SET num=num-10,VERSION =VERSION+1 WHERE VERSION=0;

4.事务 B 更改数据，但使用的是事务 B 查询到的 0 号版本，因为事务 A 已经提交版本号为 1，造成事务 B 修改失败，保证了数据的完整性。

UPDATE goods SET num=num-10,VERSION =VERSION+1 WHERE VERSION=0;

表锁机制

针对一些不支持事务的处理引擎可以使用锁表的方式控制业务。

读锁

为表设置读锁后，当前会话和其他会话都不可以修改数据，但可以读取表数据。
1.会话 A 对表 goods 设置了读锁，将不能修改该表，也不能操作其他表

LOCK TABLE goods READ;
UPDATE goods SET num=300 WHERE id=1;
SELECT * FROM stu;

2.因为会话 A 对表goods设置了读锁，所以会话 B 也不能修改

update goods set num=200 where id=1;
-- 阻塞

3.会话 A 解锁表后，其他会话又可以继续操作表了

UNLOCK TABLES;

写锁

为表设置了写锁后，当前会话可以修改，查询表，其他会话将无法操作。
1.会话 A 对表 goods 和 stu 设置写锁，本会话可以正常操作表， 并不能操作其他表

LOCK TABLE goods WRITE，stu WRITE;
INSERT INTO goods (name,num )VALUES('后盾人教程',300);

2.会话 B 读取/写入表数据都将阻塞

select * from goods

3.会话 A 解锁表数据后，其他会话都可以正常操作了

UNLOCK TABLES;