526互联

select ... for update学习

发布时间 2023-11-06 11:09:19作者: Arbitrary233

悲观锁和乐观锁

　　乐观锁和悲观锁只是两个加锁的思路，其实现方式多种多样。以下举几个在数据库中的例子。

　　对于一次的数据修改，我们可以大概将其分为三步。

  • 获取数据
  • 修改数据
  • 提交修改

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

乐观锁

　　乐观锁对数据保持一个乐观态度（大概率不会有人和我抢）。A角色获取数据、修改数据时不会对数据加锁，只有在提交修改时才会判断中间是否有其他人对数据进行了修改。（因为没有对数据加

锁，B角色可在A角色获取数据后，也获取到数据，甚至比A提前提交数据修改）。

　　一个简单的实现就是给数据添加版本号字段(此处命名为version)，例如A角色在获取数据阶段，获取到的版本号为V1，则在提交修改阶段的执行语句就为

update .......where version = V1

　　这样当数据在中途被B角色修改后，version字段将不为原来的V1，此条更新语句也就失效了。不会导致数据更新异常。

悲观锁

   悲观锁对数据保持一个悲观态度（绝逼会有人来和我抢），则在角色A获取数据后，就对数据进行了加锁，直到修改数据、提交修改后才将锁释放。中途由于数据已经被加锁，角色B压根读不数据，也

就不存在中途被修改的情况。

select … for update

　　回到正题，我们平时使用的select语句只是查询，不涉及修改，故不存在加锁的概念。而select * for update 从字面意思就可以知道，该语句不单单是查询，而是为了后续的修改，所以是要对数据加锁的，并且是悲观锁。此时按照select 中where条件字段，又可分为行锁和表锁。

　　在这里我们新建一个数据库来讨论，雇员表，销售部14人，技术部10人，共24人。（在这里留意以下人数比例。后面这个比例会影响查询，表结构和数据SQL放在最后）。

where 主键字段

　　当查询用到索引时是行锁。

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

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

where普通字段

　　当查询用没用到索引时是表锁。

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

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

进阶

  当我们使用非索引字段进行select … for update 时，会造成表锁。此时我们尝试将该非索引字段设置为索引。

  将dname设置为索引后，在会话1中执行查询dname = ‘销售部’。

  会得出结果，在会话2中我们查询销售部的员工，发现阻塞。查询非销售部的员工，可出结果。成功将表锁转化为了行锁。

  此时我们调整表中的部门，使得数据大部分为销售部

  这个时候在和上面一样做相同查询，会发现，除了销售部数据被锁定外，技术部数据也被锁定。变成了表锁。

  原因是当索引列中，所查的值占绝大部分时，会走全索引扫描，相当与将该字段看成普通字段来操作，故会形成表锁。

 

用到的sql

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', '技术部');

　　



摘自：https://blog.csdn.net/qq_32565537/article/details/126439743