[msyql]-锁机制

概述

定义

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

在数据库中，传统的计算机资源（CPU、IO、RAM）的争用外，数据也是一种供许多用户共享的资源。如何保证数据并发的访问的一致性、有效性是所有的数据库必须解决的一个问题，锁冲突也是数据库并发访问的性能的一个重要因素。从这个角度来说，锁是对数据库而言显的尤为重要，也更加复杂。

锁的分类

从对数据操作的类型（读/写）分

读锁（共享锁）：针对同一份数据，多个读操作可以统一同时进行而不会互相影响。
写锁（排他锁）：当亲写操作没有完成前，它会阻断其他写锁客读锁。

从数据操作的粒度分

表锁
行锁

1表锁（便读）

特点

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

案例分析

建表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;

表锁命令

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

#给mylock上读锁，给book上写锁
lock table my lock read, book write;

#释放表锁
unlock tables;

读锁案例

加读锁，为mylock表加read锁

#开启两个mysql会话

#会话1
#为mylock加锁(读)
lock table  mylock read;

#会话1(可以读不可写)
update mylock set name='a2' where id = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated

#会话1，中mylock上 read 期间，其他表不可以读
mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

#会话2
#在会话1对mylock上 read 期间可以读mylock与其他表

#会话2
#在会话1对mylock上 read 期间可以需要 会话1 解锁 才能对mylock进行修改

总结(读锁)

写锁案例

在会话1中添加写锁

mysql> LOCK TABLE `mylock` WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a3   |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

mysql> update mylock set name='a4' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#不能读其他表
mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

会话2

#阻塞了，需要会话一释放锁
mysql> select * from mylock;

总结（写锁）

案例总结

MyISAM引擎在执行查询语句SELECT之前，会自动给涉及到的所有表加读锁，在执行增删改之前，会自动给涉及的表加写锁。

MySQL的表级锁有两种模式：

表共享读锁（Table Read Lock）。
表独占写锁（Table Write Lock）。

対MyISAM表进行操作，会有以下情况：

対MyISAM表的读操作（加读锁），不会阻塞其他线程対同一表的读操作，但是会阻塞其他线程対同一表的写操作。只有当读锁释放之后，才会执行其他线程的写操作。
対MyISAM表的写操作（加写锁），会阻塞其他线程対同一表的读和写操作，只有当写锁释放之后，才会执行其他线程的读写操作。

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

分析

看看哪些表被锁了
show open tables;
如何分析表锁定
SHOW STATUS LIKE 'table%';
可以通过Table_locks_immediate和Table_locks_waited状态变量来分析系统上的表锁定。

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。

Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在较严重的表级锁争用情况。

此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合作为主表的引擎。因为写锁后，其他线程不能进行任何操作，大量的写操作会使查询很难得到锁，从而造成永远阻塞。

2行锁(偏写)

特点

偏向InnoDB存储引擎，开销大，加锁慢：会出现死锁；锁粒度最小，发生锁冲突的概率最低，并发度也最高。
InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁

并发事务带来的问题

更新丢失

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新题--最后的更新覆盖了由其他事务所做的更新。
例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

脏读
不可重复复
幻读

事务隔离级别

行锁案例

建表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;

读己知所写

# 会话1 

# 会话1対test_innodb_lock表做写操作，但是没有commit。
# 执行修改SQL之后，查询一下test_innodb_lock表，发现数据被修改了。
mysql> UPDATE `test_innodb_lock` SET `b` = '88' WHERE `a` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a    | b    |
+------+------+
|    1 | 88   |
|    2 | 3    |
|    3 | 4000 |
|    4 | 5000 |
|    5 | 6000 |
|    6 | 7000 |
|    7 | 8000 |
|    8 | 9000 |
+------+------+
8 rows in set (0.00 sec)

# 会话2 

# 会话2这时候来查询test_innodb_lock表。发现会话2是读不到SESSION1未提交的数据的。
mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    2 | 3    |
|    3 | 4000 |
|    4 | 5000 |
|    5 | 6000 |
|    6 | 7000 |
|    7 | 8000 |
|    8 | 9000 |
+------+------+
8 rows in set (0.00 se

两个回话同时更新同一条数据

两个回话同时更新不同行数据

回话之间互不影响

无索引(索引失效)行锁升级为表锁

varchar一定要用''。数据转换会导致索引失效

间隙锁的危害

一般情况下id尽量为连续，不要出现间隙，如果要删除使用update方法
什么是间隙锁：
- 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁是，InnoDB会个符合条件的已有数据记录的索引加锁；对于键值在添加范围内但并补存在的记录，叫做“间隙（GAP）”
  InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key）。
危害
- 因为Query执行过程中通过范围查找的话，他会锁定整个范围所有的索引键值，即使这个键值并不存在。
  间隙锁有一个比较致命的弱点，就是当锁定一个范围键值后，即使某系不存在的键值也会被无辜锁定，而造成在锁定的时候无法插入锁定范围内的任何数据。某些场景下会可能对性能造成很大的危害。

面试题：如何锁定一行？

（行锁）案例总结

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会iInnodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

行锁分析

如何分析锁定

通过检查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          | 24175 |      #从系统启动到现在总时间长度
| Innodb_row_lock_time_avg      | 12087 |      #每次等待所花平均时间
| Innodb_row_lock_time_max      | 15162 |      #从系统启动到现在等待最长的一次所花的时间
| Innodb_row_lock_waits         | 2     |      #从系统启动到现在总共等待次数
+-------------------------------+-------+

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。（varchar一定加''）
合理设计索引，尽量缩小锁的范围。
尽可能较少的检索条件，避免间隙锁。
尽量控制事务大小，减少锁定资源量和时间长度。
尽可能低级别的事务隔离。

页锁

开销与加锁时间界与表锁和行锁的之间：会出现死锁；锁定粒度界与表锁与行锁之间，并发度一般。（了解）

posted @ 2020-09-20 17:12 HankinkK 阅读(168) 评论(0) 收藏举报

刷新页面返回顶部

HankinkK