面试官:select......for update会锁表还是锁行?
程序员的成长之路
共 1226字,需浏览 3分钟
· 2021-10-25
阅读本文大概需要 2.8 分钟。
来自:blog.csdn.net/qq_42956376/article/details/109544539
select .......for update
除了有查询的作用外,还会加锁呢,而且它是悲观锁。验证:
//id为主键
//name 为唯一索引
CREATE TABLE `user` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`name` VARCHAR ( 255 ) DEFAULT NULL,
`age` INT ( 11 ) DEFAULT NULL,
`code` VARCHAR ( 255 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `idx_age` ( `age` ) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 1570068 DEFAULT CHARSET = utf8
set @@autocommit=0;
设置为手动提交。0代表手动提交,1代表自动提交。![](https://filescdn.proginn.com/21646ad8fc05c89b7c28d3387b508975/5347d6e7f0e1a8376d9f9bc95396fdfc.webp)
结合一下实例验证
图一为第一个事务,并且没有提交事务 图二为第二个事务,去更新数据,被阻塞了 图三为第二个事务,长时间拿不到锁报错。
![](https://filescdn.proginn.com/413112f6370e67eb82c1050b43f06f74/41f9e91e8b9a266f4277973db9f0a828.webp)
![](https://filescdn.proginn.com/bd2f269ae62949a61071a5859d3e5977/9eb7dca1f02da797b71d7708eae46b98.webp)
![](https://filescdn.proginn.com/29747870e8e3fb4a798660ac82d061df/13f8dfa3c729f9072aad9d62776d3a1a.webp)
![](https://filescdn.proginn.com/20e7437dfc9938f82080c96b7f21691c/d5c209b21874982260ce2aede1db3aa3.webp)
![](https://filescdn.proginn.com/ea43cbeb3418e5d5f239a1c9f5cbd6d1/047aa9ce2d1b4b0a8d33957ab410198c.webp)
![](https://filescdn.proginn.com/85c56e85947913be15e45441be7b5e20/082b7511b2cf340f6c2075e66e031511.webp)
![](https://filescdn.proginn.com/4388b863362304ec073e92713f562b3b/5fb9d4861fd781df1de7501896b35707.webp)
![](https://filescdn.proginn.com/d7babb7add99eb20c173e4122e75527b/a5de10bce207163d301bc5f5d2e751aa.webp)
![](https://filescdn.proginn.com/bc41b3ea44951ba0b11e621d8f284a50/47638597e72c0a2f1a993b051a590384.webp)
![](https://filescdn.proginn.com/a541ddb0cc9c7e94f8a9a1a43701a414/3dc0f4bc5d5e7c48dc46c99581a123ba.webp)
![](https://filescdn.proginn.com/89f7df0c9cb2c0d31020721ea3ff85bc/c6465f2a45afeb26240214b76a30c181.webp)
![](https://filescdn.proginn.com/2a6336b312b3101371daa64153a1742a/7d097ecdb8d107bbb2b8092df651595c.webp)
![](https://filescdn.proginn.com/c2b2b688606239330eefc16b589919d1/13365b8237bf6fd28fb58c489c3af9cd.webp)
结果:
select ..... for update
就会进行行锁。select ..... for update
就会进行锁表。推荐阅读:
22岁何同学自制硬核AirDesk!做了苹果放弃的产品,稚晖君点赞
最近面试BAT,整理一份面试资料《Java面试BATJ通关手册》,覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。
朕已阅
评论