面试官问:select......for update会锁表还是锁行?
程序员的成长之路
共 1569字,需浏览 4分钟
· 2022-11-22
阅读本文大概需要 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/b832991712b6a8b6313d4bcadadc6099/442d858810c53a88e8320266b3c14284.webp)
结合一下实例验证
图一为第一个事务,并且没有提交事务 图二为第二个事务,去更新数据,被阻塞了 图三为第二个事务,长时间拿不到锁报错。
![](https://filescdn.proginn.com/b870cc6428a92c8b112c76e8cb053ad6/4470b3f4792d9c198b09779859dd3175.webp)
![](https://filescdn.proginn.com/6e4278fef693017fd204ac75713c135d/9711a16a82965d0c7f018bbe6d989e4c.webp)
![](https://filescdn.proginn.com/d85909e9efe8dab7860534d47f42b075/750022f4a4c82ad31f18b72c09449a56.webp)
![](https://filescdn.proginn.com/b1b1c89bd4b9df6ab57616a18cd0b962/4598b675731d834803df11c91c271a10.webp)
![](https://filescdn.proginn.com/addd86ce217a448306c714540e7cd6f3/729aa7f44c95f0b5229bdc556ea123b2.webp)
![](https://filescdn.proginn.com/2838db01f211e31d6ad6ace0c064fc75/f4121ec58462c31d35c725c1459d412a.webp)
![](https://filescdn.proginn.com/fbd4a0d994db8983ebffefe2a34700ce/00e7eafc27a0c24672df90076d048986.webp)
![](https://filescdn.proginn.com/b10df0d8ac98035c96b8a5b9f301e4b6/0a8d87d1c8fa69798ced1f8a51519d74.webp)
![](https://filescdn.proginn.com/2f8ab8a4ae765f472855e89248b60fc3/fbd51739e1cf760624bbed68b67e7e83.webp)
![](https://filescdn.proginn.com/55bfc675188bd7c3253ea2090ca9a537/225186c523d4cc8f5113b0170f2d7285.webp)
![](https://filescdn.proginn.com/6b17deb286612bb8cd3e3f32ca26d73c/1d7650d1b432d88fd617d3968b05d04b.webp)
![](https://filescdn.proginn.com/e8dcca694cf515604caddaba9a85dcec/28180470dbde839599012cce262d1bbb.webp)
![](https://filescdn.proginn.com/c043a96abdfa1a4ae6029a4a833a16f8/bd7689ce889421082a3a535a5853f43d.webp)
结果:
select ..... for update
就会进行行锁。select ..... for update
就会进行锁表。推荐阅读:
互联网初中高级大厂面试题(9个G) 内容包含Java基础、JavaWeb、MySQL性能优化、JVM、锁、百万并发、消息队列、高性能缓存、反射、Spring全家桶原理、微服务、Zookeeper......等技术栈!
⬇戳阅读原文领取! 朕已阅
评论