mysql 锁机制

mysql 锁机制

1.从数据库上的操作来说：分为读写锁

读锁（也称之为共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

写锁（也称之为排他锁）：当前时刻，只能有一个线程进行写入操作，也就是当前线程没有释放，它就会阻断其他的线程写入。（死锁产生原因之一：互斥）

2.从数据库上的粒度来说：分为表锁、行锁

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

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

手动添加表锁

lock table table_name read(write)

//查询数据库中表是否被锁表中

show open tables;

手动给数据库表上表锁（读锁、写锁）

lock table xxxxx_xx read, xxxx_xx write;

手动给数据库解除表锁

unlock table_name;

手动解除所有数据库表锁

unlock tables;

MyISAM存储引擎锁

读锁存在问题：

1.如果会话1对表a增加了读锁，会话1可以读取表a，但是不能写入表a，不能读取表b，不能写入表b；

此刻会话2可以读取表a，写入表a会一直阻塞sql，能够自由操作表b

只有会话1释放锁，那么会话2才不会阻塞

写锁存在的问题：

2.如果会话1对表a增加了写锁，会话1可以读取表a，能够写入表a，不能够读取表b，不能够写入表b；

此刻会话2读取表a会一直阻塞sql，写入表a会一直阻塞sql，能够自由操作表b

只有会话1释放锁，那么会话2才不会阻塞

总结：

MyISAM存储引擎会在执行查询语句之前，自动将所有涉及到的表增加读锁，产生的效果如上所所述

那么在执行增删改操作的执行语句之前，会自动给涉及的表增加写锁，产生效果如上

1.在MyISAM存储引擎的表中的读操作，不会阻塞其他进程对该表的读操作，但是会阻塞其他进程对该表的写操作。只有当前进程释放了当前读锁，那么就会执行其他进程的写操作。

2.在MyISAM存储引擎的表中进行写操作，会阻塞其他进程对该表的读写操作，只有当前进行对该表的锁进行释放了，才会执行其他进程的读写操作

换句话锁来说，读锁会阻塞其他进程的写，写锁会阻塞其他进程的读写；

分析表的锁定：

SQL：show status like 'table%';

状态记录表锁情况

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

Table_locks_waited：出现表级锁定争抢而发生的等待次数

Table_open_cache_hits：打开表的缓存的查找的命中的数量 Table_open_cache_misses：打开表缓存的查找的未命中的数量 Table_open_cache_overflows：打开表的缓存溢出的数量

所以可以总结出，MyISAM存储引擎的读写锁调度是写优先，所以我们常说，MyISAM不适合使用为写操作的表的存储引擎原因。因为写锁操作后，其他线程都会进行阻塞操作，大量的更新会使得查询很难得到锁，从而造成长久阻塞。（此特性我们可以根据具体业务将表进行拆分，分为多个表，写表和读表）

InnoDB存储引擎锁：

其实此部分锁，就是行锁；

行锁最重要的体现就是隔离级别；

在可重复读隔离级别中会出现 行锁 上升至 表锁过程，产生原因，就是事务中修改条件 索引失效，或者说，没有使用到索引。

间隙锁：

当更新条件中，存在一个更新范围，将会产生间隙锁。

换句话说，就是当我们执行更新语句的条件为一个范围的时候，而不是使用常量作为条件，这个时候执行共享、排他锁，innodb就会对符合条件的数据进行上锁保护；对于数据库中不存在的数据，但是属于该范围的数据，称之为间隙“GAP”，InnoDB也会对其加锁保护，这种操作称之为间隙锁“Next-key锁”

举例说明：例如数据库中存在数据1,2,4,5,6；那么事务A更新的是id为1<= >=6的数据，事务B新增数据为3的数据，那么这个时候事务A未提交，那么事务B就会被阻塞。这就是实例。

产生的危害或大或小，假如事务A对时间范围为当天的数据产生了间隙锁，那么这个时候再去新增当天的范围数据，那么这个时候，其他事务就都会被阻塞在这，是一种线上环境数据事故问题了。虽然比表锁危机小一些，但是也是一个很大的危机了。

主动锁定某一行数据（同上述的锁定一张表）

begin;

select * from table_name where key = value for update;

commit;

上述三条脚本就是锁定一条数据的方式，当执行完commit；将会解锁

监控数据库中产生异常数据的指标，我们可以通过命令实现查询

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：从系统启动到现在因为锁而产生阻塞的总次数

如果上述数据存在异常，那么这个时候，我们就需要对数据库整体进行分析了。看看整个数据库的执行情况。

现在，看看我的结果，全是0，漂亮！没有用户，哈哈

总结：

innodb存储引擎实现的行级锁定，相对而言比表级锁定要消耗更多的性能，但是在现在的这个时代，物理机的配置应该不是问题的。所以说innodb在互联网项目的是一种很高效的存在，解决了高并发问题。但是这innodb也要依赖于我们的使用方式，如果使用不当，锁级别上升、间隙锁，都会产生致命事故。

那么关于上述使用不当的情况，我们需要尽量对查询，更新，都使用索引方式进行，避免锁隐式升级。

尽量控制事务大小（很多项目基本不考虑事务）

尽可能降低事务隔离级别，使得数据库吞吐性能提升

还有记住！间隙锁，条件的范围，不能太大，不然。。。。

此处引申出一种页锁，顾名思义，就是一页数据的锁定，介于行锁与表锁之间。