乐趣区

关于数据库:MySQL-行锁和表锁的含义及区别

jiezi

不可能天天都是好日子，有了不顺心的日子，好日子才会闪闪发亮。

一、前言

对于行锁和表锁的含意区别，在面试中应该是高频呈现的，咱们应该对 MySQL 中的锁有一个零碎的意识，更具体的须要自行查阅材料，本篇为概括性的总结答复。

MySQL 罕用引擎有 MyISAM 和 InnoDB，而 InnoDB 是 mysql 默认的引擎。MyISAM 不反对行锁，而 InnoDB 反对行锁和表锁。

如何加锁？

MyISAM 在执行查问语句（SELECT）前，会主动给波及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会主动给波及的表加写锁，这个过程并不需要用户干涉，因而用户个别不须要间接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

显式加锁：

上共享锁（读锁）的写法：lock in share mode，例如：

select  math from zje where math>60 lock in share mode；

上排它锁（写锁）的写法：for update，例如：

select math from zje where math >60 for update；

二、表锁

不会呈现死锁，产生锁抵触几率高，并发低。

MyISAM 引擎

MyISAM 在执行查问语句（select）前，会主动给波及的所有表加读锁，在执行增删改操作前，会主动给波及的表加写锁。

MySQL 的表级锁有两种模式：

  • 表共享读锁
  • 表独占写锁

读锁会阻塞写，写锁会阻塞读和写

  • 对 MyISAM 表的读操作，不会阻塞其它过程对同一表的读申请，但会阻塞对同一表的写申请。只有当读锁开释后，才会执行其它过程的写操作。
  • 对 MyISAM 表的写操作，会阻塞其它过程对同一表的读和写操作，只有当写锁开释后，才会执行其它过程的读写操作。

MyISAM 不适宜做写为主表的引擎，因为写锁后，其它线程不能做任何操作，大量的更新会使查问很难失去锁，从而造成永远阻塞

三、行锁

会呈现死锁，产生锁抵触几率低，并发高。

在 MySQL 的 InnoDB 引擎反对行锁，与 Oracle 不同，MySQL 的行锁是通过索引加载的，也就是说，行锁是加在索引响应的行上的，要是对应的 SQL 语句没有走索引，则会全表扫描，行锁则无奈实现，取而代之的是表锁，此时其它事务无奈对以后表进行更新或插入操作。

CREATE TABLE `user` (`name` VARCHAR(32) DEFAULT NULL,
`count` INT(11) DEFAULT NULL,
`id` INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

-- 这里，咱们建一个 user 表，主键为 id



-- A 通过主键执行插入操作，但事务未提交
update user set count=10 where id=1;
-- B 在此时也执行更新操作
update user set count=10 where id=2;
-- 因为是通过主键选中的，为行级锁，A 和 B 操作的不是同一行，B 执行的操作是能够执行的



-- A 通过 name 执行插入操作，但事务未提交
update user set count=10 where name='xxx';
-- B 在此时也执行更新操作
update user set count=10 where id=2;
-- 因为是通过非主键或索引选中的，降级为为表级锁，-- B 则无奈对该表进行更新或插入操作，只有当 A 提交事务后，B 才会胜利执行

for update

如果在一条 select 语句后加上 for update，则查问到的数据会被加上一条排它锁，其它事务能够读取，但不能进行更新和插入操作 

-- A 用户对 id= 1 的记录进行加锁
select * from user where id=1 for update;

-- B 用户无奈对该记录进行操作
update user set count=10 where id=1;

-- A 用户 commit 当前则 B 用户能够对该记录进行操作

行锁的实现须要留神：

  1. 行锁必须有索引能力实现，否则会主动锁全表，那么就不是行锁了。
  2. 两个事务不能锁同一个索引。
  3. insert，delete，update 在事务中都会主动默认加上排它锁。

行锁场景：

A 用户生产，service 层先查问该用户的账户余额，若余额足够，则进行后续的扣款操作；这种状况查问的时候应该对该记录进行加锁。

否则，B 用户在 A 用户查问后生产前先一步将 A 用户账号上的钱转走，而此时 A 用户曾经进行了用户余额是否足够的判断，则可能会呈现余额曾经有余但却扣款胜利的状况。

为了防止此状况，须要在 A 用户操作该记录的时候进行 for update 加锁

扩大：间隙锁

当咱们用范畴条件而不是相等条件检索数据，并申请共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范畴内并不存在的记录，叫做间隙

InnoDB 也会对这个 ” 间隙 ” 加锁，这种锁机制就是所谓的间隙锁 

-- 用户 A
update user set count=8 where id>2 and id<6

-- 用户 B
update user set count=10 where id=5;

如果用户 A 在进行了上述操作后，事务还未提交，则 B 无奈对 2~6 之间的记录进行更新或插入记录，会阻塞，当 A 将事务提交后，B 的更新操作会执行。

倡议：

  • 尽可能让所有数据检索都通过索引来实现，防止无索引行锁降级为表锁
  • 正当设计索引，尽量放大锁的范畴
  • 尽可能减少索引条件，防止间隙锁
  • 尽量管制事务大小，缩小锁定资源量和工夫长度
退出移动版