526互联

mysql数据库的锁-select for update

发布时间 2023-06-06 18:18:42作者: allenwork

乐观锁与悲观锁

乐观锁和悲观锁只是两个加锁的思路，其实现方式多种多样。以下举几个在mysql数据库中的例子。
  
对于一次的数据修改，我们可以大概将其分为三步：

  1. 获取数据

  2. 修改数据

  3. 提交修改

乐观锁

假设A、B两个角色对数据进行修改：

  • 乐观锁对数据保持一个乐观态度（大概率不会有人和我抢）。

  • A角色获取数据、修改数据时不会对数据加锁，只有在提交修改时才会判断中间是否有其他人对数据进行了修改。（因为没有对数据加锁，B角色可在A角色获取数据后，也获取到数据，甚至比A提前提交数据修改）。

悲观锁

假设A、B两个角色对数据进行修改：

  • 悲观锁对数据保持一个悲观态度（绝对会有人来和我抢），则在角色A获取数据后，就对数据进行了加锁，直到修改数据、提交修改后才将锁释放。

  • 中途由于数据已经被加锁，角色B压根读不数据，也就不存在中途被修改的情况。

Mysql数据库的锁

select ... for update

  • 回到正题，我们平时使用的select语句只是查询，不涉及修改，故不存在加锁的概念。

  • 而select * for update 从字面意思就可以知道，该语句不单单是查询，而是为了后续的修改，所以是要对数据加锁的，并且是悲观锁。

  • 此时按照 select 中 where条件字段，又可分为 行锁 和 表锁 。

在这里我们新建一个数据库来讨论，雇员表，销售部14人，技术部10人，共24人。


-- 创建表
CREATE TABLE `employee` (
  `empno` int(11) NOT NULL,
  `ename` varchar(255) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL,
  `sal` varchar(255) DEFAULT NULL,
  `dname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `dname_index` (`dname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (111, '销售1', '销售', '2022-08-20 16:14:18', '800', '销售部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (112, '销售2', '销售', '2022-08-20 16:14:12', '80012', '销售部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (113, '销售3', '销售', '2022-08-20 16:14:13', '80013', '销售部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (114, '销售4', '销售', '2022-08-20 16:14:14', '80014', '销售部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (115, '销售5', '销售', '2022-08-20 16:14:15', '80015', '销售部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (116, '销售6', '销售', '2022-08-20 16:14:16', '80016', '销售部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (117, '销售7', '销售', '2022-08-20 16:14:17', '80017', '销售部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (118, '销售8', '销售', '2022-08-20 16:14:18', '80018', '销售部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (119, '销售9', '销售', '2022-08-20 16:14:19', '80019', '销售部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (120, '销售10', '销售', '2022-08-20 16:14:20', '80020', '销售部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (121, '销售11', '销售', '2022-08-20 16:14:21', '80021', '销售部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (122, '销售12', '销售', '2022-08-20 16:14:22', '80022', '销售部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (123, '销售13', '销售', '2022-08-20 16:14:23', '80023', '销售部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (124, '销售14', '销售', '2022-08-20 16:14:24', '80024', '销售部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (125, '技术1', '技术', '2022-08-20 16:14:25', '80025', '技术部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (126, '技术2', '技术', '2022-08-20 16:14:26', '80026', '技术部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (127, '技术3', '技术', '2022-08-20 16:14:27', '80027', '技术部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (128, '技术4', '技术', '2022-08-20 16:14:28', '80028', '技术部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (129, '技术5', '技术', '2022-08-20 16:14:29', '80029', '技术部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (130, '技术6', '技术', '2022-08-20 16:14:30', '80030', '技术部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (131, '技术7', '技术', '2022-08-20 16:14:31', '80031', '技术部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (132, '技术8', '技术', '2022-08-20 16:14:32', '80032', '技术部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (133, '技术9', '技术', '2022-08-20 16:14:33', '80033', '技术部');
INSERT INTO `test`.`employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `dname`) VALUES (134, '技术10', '技术', '2022-08-20 16:14:34', '80034', '技术部');

where 主键字段

当查询用到索引时是行锁

  • 我们在会话1中采用主键来查询一条信息，可正常显示结果。由于没有执行rollback，该事务没有退出，此时该条数据被行锁。

  • 新建一个会话2同样通过主键查询主键为111和112的数据。此时由于会话1行锁了数据111，会导致会话2中的查询111被阻塞。而会话2查询112可正常查出。

where普通字段

当查询用到普通字段时是表锁

  • 我们在会话1中采用普通字段来查询一条信息，可正常显示结果。由于没有执行rollback，该事务没有退出，此时表表被表锁。

  • 我们在会话2中采用普通字段来查询一条信息，发现无论使用什么来查询，均被阻塞。

会话1对应的sql：


-- 普通查询，不涉及任何的锁
select * from employee


-- 在会话中,想要使用锁,必须使用begin开头,否则认为一条语句执行完就提交结束了,观察不到锁的作用
select * from employee for update;


-- 开启会话
begin;
-- 	select * from employee where empno = '111' for update;
-- 	select * from employee where ename = '销售14' for update;
	select * from employee where dname = '销售部' for update;


-- 回滚
rollback;

会话2对应额sql：

-- 开启会话
begin;

-- 主键
-- 	select * from employee where empno = '111' for update;
-- 	select * from employee where empno = '112' for update;

-- 普通建
-- 	select * from employee where ename = '销售14' for update;
-- 	select * from employee where ename = '销售13' for update;
-- 	select * from employee where ename in ('销售13','销售14');

-- 索引
-- 	select * from employee where dname = '销售部' for update;
	select * from employee where dname = '技术部' for update;

rollback;