mysql的表锁

一 mysql的锁

1.1 锁的概念

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

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

1.2 锁的分类

从对数据操作的类型

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

从对数据操作的粒度分

• 表锁
• 行锁

1.3 表锁

1.3.1 表锁的特点

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

1.3.2 上锁的过程

lock table 表名字 read(write), 表名字2 read(write), 其他;

 1.3.3 查看表加过的锁

show open tables;

  1.3.4 释放表加过的锁

     unlock tables;

1.4 表锁的案例

1.4.1 案例情况

情况1：如果A加了读锁

A可以查自已的表，但不能更新或者增加自已的表数据，不能查看其他表的数据。

 B可以查A的表，但不能更新或者增加A的表数据（会阻塞），直到A释放了读锁，直到A释放锁，B才可以修改不然就一直阻塞。

 

 情况二：如果A加了写锁

A可以查自已的表，也可以更新或者添加自已的表数据。

 B不可以查A的表，也不能更新或者增加A的数据（会阻塞），直到A释放了写锁

 1.4.2 结论

对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

对MyISAM表的写操作〈加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

简而言之：表级下锁的读写锁就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

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

Mysql中各类锁的机制图文详细解析（全）_码农研究僧的博客-CSDN博客

1.5 行锁

1.5.1 行锁概念

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

InnoDB与MyISAM的最大不同有两点：一是支持事务(TRANSACTION)；二是采用了行级锁 

1.5.2 行锁的案例 

1.新建一张表

CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;

2.添加一部分数据

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

3.创建两个索引

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;该命令是开启手动提交事务，只需要将其自动提交关闭即可，即可操作行锁。

情况一：两个进程都开启手动提交，而且修改同一行数据

假设A进程修改了事务，B事务也进行修改的话，进程会被阻塞

 只有当A提交了事务之后，B进程才会解除阻塞

 情况二： 两个进程都开启手动提交，修改不同行数据

都是可以的，但是事务不一样，只有都提交的时候，才会更新，两者才能看到自己的修改和对方的修改。

  情况三： 锁表的情况

索引失效行锁变为表锁，出现锁表的情况，其他进程无法操作。

类型转换导致索引失效，失效之后只能全表锁定，因为innodb是索引加锁。行锁变表锁

由于表锁了，即使A提交了事务，B想修改事务（即使不同行也回阻塞），也会被阻塞。只有等他到表锁他自动解除。

1.5.3 行锁的总结

innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MylISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

1.5.4 分析行级锁定

通过检查lnnoDB_row_lock状态变量来分析系统上的行锁的争夺情况，通过命令行进行分析：show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time：从系统启动到现在锁定总时间长度；（等待总时长）
Innodb_row_lock_time_avg：每次等待所花平均时间；（等待平均时长）
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_waits：系统启动后到现在总共等待的次数；（等待总次数）

1.5.5 锁定表中一行数据

如何锁定一行的数据（排他锁）

• begin
• sql数据 for update
• commit

 

一个是查询： select * from test_innodb_lock where a=8 for update

另一个是修改： update test_innodb_lock set b='8001' where a=8

只有查询session的会话commit 才不会阻塞，修改的session会话才能提交。。。。

添加共享锁是：
加共享锁可以使用select ... lock in share mode语句

1.5.6 表锁的优化

具体可以通过如下进行优化：

1尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。

2合理设计索引，尽量缩小锁的范围

3尽可能较少检索条件，避免间隙锁

4尽量控制事务大小，减少锁定资源量和时间长度

5尽可能低级别事务隔离

1.6 间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。加了间隙锁，但是想添加间隙的东西，发现不可修改。

InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

 缺点是：

因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

1.7 页级锁

页级锁：是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折中的页级锁，一次锁定相邻的一组记录。BDB 支持页级锁。

特点：

开销和加锁时间界于表锁和行锁之间；

会出现死锁；

锁定粒度界于表锁和行锁之间，并发度一般。

1.8 页面中各种锁的比较

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。