面试官问:select......for update会锁表还是锁行?
JAVA葵花宝典
共 861字,需浏览 2分钟
· 2021-11-14
来源:网络
select查询语句是不会加锁的,但是select .......for update
除了有查询的作用外,还会加锁呢,而且它是悲观锁。那么它加的是行锁还是表锁,这就要看是不是用了索引/主键。没用索引/主键的话就是表锁,否则就是是行锁。
验证:
建表sql需要关闭自动提交,通过//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代表自动提交。![b75cb86db872f9cad51540b2795dc8ce.webp](https://filescdn.proginn.com/cc908b5936afd1679421853e94a948d6/b75cb86db872f9cad51540b2795dc8ce.webp)
结合一下实例验证
实例1:使用主键id为条件去查询,然后开启另一个事务去更新数据,更新被阻塞,加锁了,锁定要查询的id为1的行数据。- 图一为第一个事务,并且没有提交事务
- 图二为第二个事务,去更新数据,被阻塞了
- 图三为第二个事务,长时间拿不到锁报错。
![9bba6b4e9759e05c149203c1ec7fc19d.webp](https://filescdn.proginn.com/ad481086e6c23353d19672e2ebc09729/9bba6b4e9759e05c149203c1ec7fc19d.webp)
![87992ecc76a2d22939aeae2101f7e89d.webp](https://filescdn.proginn.com/773ce2c348806ef72aacbc6380557bef/87992ecc76a2d22939aeae2101f7e89d.webp)
![2850613fd12dd4e8b44af306b4179e45.webp](https://filescdn.proginn.com/ca9c1d8ede53723753257b826d7649e2/2850613fd12dd4e8b44af306b4179e45.webp)
![d0ea5190ffa8f4ffcdfa3b63bea5bf0e.webp](https://filescdn.proginn.com/a635e2917a557d7cfba5df399fada232/d0ea5190ffa8f4ffcdfa3b63bea5bf0e.webp)
![4e0e030cd3837b97bdaa3930bdc8df55.webp](https://filescdn.proginn.com/ae0a83bf259b9489870c4794005dad83/4e0e030cd3837b97bdaa3930bdc8df55.webp)
![c7cd7d330d667197960373a787acf507.webp](https://filescdn.proginn.com/e2e30ca83edd284416b2fc40f4f10b50/c7cd7d330d667197960373a787acf507.webp)
![7f4116ba538b5cdc6ae4c2beac8204c4.webp](https://filescdn.proginn.com/38ea7408381bdd86169b33f845c9fa6b/7f4116ba538b5cdc6ae4c2beac8204c4.webp)
![034c58e781d1ee0e9a8c5979337c355b.webp](https://filescdn.proginn.com/a7011b96a0533ca440b306e3db800194/034c58e781d1ee0e9a8c5979337c355b.webp)
![3eb934441f6ba7217ea4ec86314d8512.webp](https://filescdn.proginn.com/fa0228d1bc82fa42e2eaa74974da11c0/3eb934441f6ba7217ea4ec86314d8512.webp)
![efa3ee465daf2bfefab8e28c4bb8ee59.webp](https://filescdn.proginn.com/d1af9262fe47b54f4e734f4f5d1f5785/efa3ee465daf2bfefab8e28c4bb8ee59.webp)
![2ed6b2b4f07c6231a4316b6603a11196.webp](https://filescdn.proginn.com/456c32fc54151a73db47d37753f3ceac/2ed6b2b4f07c6231a4316b6603a11196.webp)
![1837830050d2c6c2788d55c272ccc2ae.webp](https://filescdn.proginn.com/f29accb533a3941d2d1a84e1d098dd04/1837830050d2c6c2788d55c272ccc2ae.webp)
![71c78d0c41c80cb597ea0e0b095d408d.webp](https://filescdn.proginn.com/0ec0cdb863d913519abd37da16b26d2e/71c78d0c41c80cb597ea0e0b095d408d.webp)
结果:
如果查询条件用了索引/主键,那么select ..... for update
就会进行行锁。如果是普通字段(没有索引/主键),那么select ..... for update
就会进行锁表。评论