Mysql锁机制理解

第一步：创建表

CREATE TABLE `test_transaction` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(50) DEFAULT NULL,

`age` INT(11) DEFAULT NULL,

`height` INT(11) DEFAULT NULL,

`score` INT(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

添加记录如下：

 

-- 对数据库表加锁

-- 本身会话，若执行写操作，直接报错：Table 'test_transaction' was locked with a READ lock and can't be updated

-- 其它会话，若执行写操作，会等待，直到解除数据库读锁

LOCK TABLE test_transaction READ;

-- 当前会话增删改查 都可执行；

表锁

-- 其它会话所有对该表的操作均阻塞，包括锁表操作

LOCK TABLE test_transaction WRITE;

 

-- 对数据库表解锁。

-- 解锁仅局限于当前会话的锁解了。如果有两个会话A，B 同时 对表test_transaction 加了读锁，那么当A 执行解锁后，A依然无法插入或者更新，直到表test_transaction的锁全部解开。

UNLOCK TABLES ;

 

 

-- 查看表上加过的锁 (该操作会展示该Mysql下 所有数据库 所有表

-- In_use 0:代表没有锁；大于1：代表有锁 n 代表几把锁。

-- Name_locked

)

SHOW OPEN TABLES;

 

-- 查看当前数据库的事务隔离级别

-- read-uncommitted 读未提交

-- read-committed 读已提交

-- REPEATABLE-READ 可重复读（mysql 默认隔离级别）

-- Serializable 可串行化

SHOW VARIABLES LIKE 'tx_isolation';

 

 

锁分类

从性能上分为乐观锁和悲观锁

从数据库操作类型分 读锁和写锁（都属于悲观锁）

读锁-共享锁（S锁）；写锁-排它锁（X锁）

 

 

前方高能！！！

间隙锁（Gap Lock）

间隙锁，锁的就是两个值之间的空隙，而且它在某些情况下可以解决幻读问题。

（1,3）,（3,10),(10,12),(12,正无穷)

 

假如现在我开启一个事务：

 

BEGIN

 

UPDATE test_transaction SET score=996 WHERE id>4 AND id < 11 ;

.

在这个过程中再次开启一个会话，那么 （3,12]之间的任何更新以及插入都将阻塞;

.

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:系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：Innodb_row_lock_time_avg （等待平均时长）Innodb_row_lock_waits （等待总次数）Innodb_row_lock_time（等待总时长）尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

注：如果事务中更新的表字段没有索引，那么行级锁会升级为表锁。---》因为锁针对的是索引，不是行数据。