mysql总结(一)-锁

寒滴空阶
202 阅读4分钟

mysql建表语句

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `mobile` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `sex` tinyint(255) DEFAULT NULL,
  `register_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

全局锁

Flush tables with read lock

进行非读操作的全库锁定,备份的时候使用,当使用Innodb的数据库,因为存在事务操作和MVCC操作,因此innodb存在事务,因此备份的时候不需要使用此操作,对于没有事务操作的Mysiam,需要使用

表级锁

表级锁分为 表锁和元数据锁

表锁 lock tables … read/write 释放锁 unlock tables

表锁 MDL（metadata lock) 这个锁是不需要显示加的,在访问一个表的时候会自动加上, MDL的作用是保持读写的正确,当对一个数据进行循环读取的时候,执行期间另外一个线程对表结构进行更改,如果不加MDL锁,读取结果将会不正确。在** MySQL 5.5 **版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁

MDL的实验


set global autocommit=0 // 关闭事务全局自动提交


//session A 
begin;
SELECT * from users 
//session B
SELECT * from users 
//session C
ALTER TABLE users ADD abcd VARCHAR(20) NULL
//session D 
SELECT * from users

session A先启动 这个时候会对users表加一个DML的读锁,session B因此是申请的MDL读锁,因此可以正常执行.session C需要的是DML写锁,因为A的读锁还没有释放,因此C阻塞。D需要的也是申请读锁,但因为C需要写锁,已经进行阻塞了,因此D也阻塞.所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了

Mysql行锁

两阶段锁

//session A 
begin;
UPDATE users set sex = sex +1  WHERE id=1;
UPDATE users set sex = sex +2  WHERE id=2;

// session B 
begin;
UPDATE users set sex = sex +1  WHERE id=1;

sessionA持有ID=1和ID=2的锁,sessionB对id=1这一行的操作会被锁定,只有当sessionA进行commit提交之后,sessionB的操作才会执行,有效的防止了并发

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议

Mysql死锁

死锁的模拟

// session A 
begin;
UPDATE users set sex = sex +1  WHERE id=1;

// session B 
begin;
UPDATE users set sex = sex +1  WHERE id=2

// session A 
UPDATE users set sex = sex +2 where id = 2;

// session B 
UPDATE users set sex = sex=2 where id = 1

事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态

解决死锁的方式 一般分为两种

  1. 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置 mysql默认50s,设置太小的如果不是死锁,直接解开会有问题
  2. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

间隙锁

幻读发生的场景

image

  1. Q1 只返回 id=5 这一行；
  2. 在 T2 时刻，session B 把 id=0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id=0 和 id=5 这两行；
  3. 在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id=0、id=1 和 id=5 的这三行

幻读的定义:

  1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。
  2. 幻读在“当前读”下才会出现。上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

innodb解决幻读

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

间隙锁，锁的就是两个值之间的空隙。比如文章开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙

当你执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。也就是说这时候，在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁

avatar
文章
阅读
粉丝