如何锁住SELECT语句所影响的记录？

csdyyr 2009-08-12 04:09:14
默认隔离级别下，在事务中，如果SELECT某个表只有一行记录，就只锁住这一行，其它事务不可读不可改这一行，直到事务结束，但可以读可以改该表的其它行，如何实现？
...全文
566 41 打赏 收藏 转发到动态 举报
写回复
用AI写文章
41 条回复
切换为时间正序
请发表友善的回复…
发表回复
xiaomeixiang 2009-08-14
  • 打赏
  • 举报
 回复
学习
samyou 2009-08-14
  • 打赏
  • 举报
 回复
学习了
guguda2008 2009-08-13
  • 打赏
  • 举报
 回复
MARK，好好读读
DengXingJie 2009-08-13
  • 打赏
  • 举报
 回复
友情幫頂
csdyyr 2009-08-13
  • 打赏
  • 举报
 回复
[Quote=引用 16 楼 jinjazz 的回复:]
SQL codeif(object_id('t')isnotnull)droptable tgocreatetable t(idint)insertinto tselect1unionselect2unionselect3begintranselecttop1*from twith(updlock,readpast)--commit--返回 1--新开一个连接begintranselecttop1¡­
[/Quote]
剪剪的虽然可以， 不过我不想在新的连接（另一个会话）中使用锁定提示。
csdyyr 2009-08-13
  • 打赏
  • 举报
 回复
[Quote=引用 30 楼 zwzw911 的回复:]
引用 29 楼 xman_78tom 的回复:
引用 22 楼 zwzw911 的回复:
LZ搞错了。
  在默认的read commit的隔离级别，当使用select对某个记录进行读取的时候，在这个记录上加的是共享锁，当记录读取完毕，共享锁立即释放。而共享锁的作用，只是防止其它进程中的事务，对当前的记录进行修改，而读取是不受限制的。
  9楼的例子就很好的说明了这点，即使一个没有结束的事务通过select对记录添加了共享锁，但是第二个事务中的select还是能够读取这个记录。
  而当使用update进行记录更新的时候，会使用到两个锁，首先是共享锁，这个锁发生在查找记录的时候（要更新记录就得先找到记录），然后进行更新的收候，对记录添加排他锁，当添加了排他锁之后，其它进程中/事务中的SQL就无法对记录进行读取和更新了。
  将9楼的第一个事务中的select改成update，然后执行第二个事务，就可以看到第二个事务被阻塞了

看清楚 select * from authorswith (xlock) where au_id='172-32-1176'，使用的是“表提示”手动为记录加锁。
不知道楼主的用在什么地方，我的建议是使用 update 锁，串行化数据的修改，可以避免私锁问题。


惭愧，光顾着抢分了……。
估计适合隔离级别有关。在read commited下，select读取到数据之后，立刻释放锁，而不管事务是否已经结束。
LZ把隔离级别提高到可重复读或者可序列化读，在试试
[/Quote]
呵呵，提高隔离级别对并发操作影响较大。
csdyyr 2009-08-13
  • 打赏
  • 举报
 回复
[Quote=引用 29 楼 xman_78tom 的回复:]
引用 22 楼 zwzw911 的回复:
......

看清楚 select * from authorswith (xlock) where au_id='172-32-1176'，使用的是“表提示”手动为记录加锁。
不知道楼主的用在什么地方，我的建议是使用 update 锁，串行化数据的修改，可以避免私锁问题。
[/Quote]
用with (xlock)只是加了IX锁（意向排它锁），所以不会阻塞其它事务的SELECT,而用with(xlock,paglock)则加了X锁（排它锁）,这样阻塞其它事务。看来READ COMMITTED隔离级别不可以锁住单行？
begin tran
select * from authors with (xlock) where au_id='172-32-1176'

--rollback tran
sp_lock:
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
57 5 0 0 DB S GRANT
58 5 1977058079 1 PAG 1:197 IX GRANT
58 5 0 0 DB S GRANT
58 5 1977058079 1 KEY (02014f0bec4e) X GRANT
58 1 85575343 0 TAB IS GRANT
58 5 1977058079 0 TAB IX GRANT
59 5 0 0 DB S GRANT


begin tran
select * from authors with (xlock,paglock) where au_id='172-32-1176'
sp_lock:
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
57 5 0 0 DB S GRANT
58 5 1977058079 1 PAG 1:197 X GRANT
58 5 0 0 DB S GRANT
58 1 85575343 0 TAB IS GRANT
58 5 1977058079 0 TAB IX GRANT
59 5 0 0 DB S GRANT


zwzw911 2009-08-13
  • 打赏
  • 举报
 回复
[Quote=引用 29 楼 xman_78tom 的回复:]
引用 22 楼 zwzw911 的回复:
LZ搞错了。
在默认的read commit的隔离级别，当使用select对某个记录进行读取的时候，在这个记录上加的是共享锁，当记录读取完毕，共享锁立即释放。而共享锁的作用，只是防止其它进程中的事务，对当前的记录进行修改，而读取是不受限制的。
9楼的例子就很好的说明了这点，即使一个没有结束的事务通过select对记录添加了共享锁，但是第二个事务中的select还是能够读取这个记录。
而当使用update进行记录更新的时候，会使用到两个锁，首先是共享锁，这个锁发生在查找记录的时候（要更新记录就得先找到记录），然后进行更新的收候，对记录添加排他锁，当添加了排他锁之后，其它进程中/事务中的SQL就无法对记录进行读取和更新了。
将9楼的第一个事务中的select改成update，然后执行第二个事务，就可以看到第二个事务被阻塞了

看清楚 select * from authorswith (xlock) where au_id='172-32-1176'，使用的是“表提示”手动为记录加锁。
不知道楼主的用在什么地方，我的建议是使用 update 锁，串行化数据的修改，可以避免私锁问题。
[/Quote]

惭愧，光顾着抢分了……。
估计适合隔离级别有关。在read commited下，select读取到数据之后，立刻释放锁，而不管事务是否已经结束。
LZ把隔离级别提高到可重复读或者可序列化读，在试试
xman_78tom 2009-08-13
  • 打赏
  • 举报
 回复
[Quote=引用 22 楼 zwzw911 的回复:]
LZ搞错了。
在默认的read commit的隔离级别，当使用select对某个记录进行读取的时候，在这个记录上加的是共享锁，当记录读取完毕，共享锁立即释放。而共享锁的作用，只是防止其它进程中的事务，对当前的记录进行修改，而读取是不受限制的。
9楼的例子就很好的说明了这点，即使一个没有结束的事务通过select对记录添加了共享锁，但是第二个事务中的select还是能够读取这个记录。
而当使用update进行记录更新的时候，会使用到两个锁，首先是共享锁，这个锁发生在查找记录的时候（要更新记录就得先找到记录），然后进行更新的收候，对记录添加排他锁，当添加了排他锁之后，其它进程中/事务中的SQL就无法对记录进行读取和更新了。
将9楼的第一个事务中的select改成update，然后执行第二个事务，就可以看到第二个事务被阻塞了
[/Quote]
看清楚 select * from authors with (xlock) where au_id='172-32-1176'，使用的是“表提示”手动为记录加锁。
不知道楼主的用在什么地方，我的建议是使用 update 锁，串行化数据的修改，可以避免私锁问题。
xman_78tom 2009-08-13
  • 打赏
  • 举报
 回复
通过“事件跟踪器”的跟踪。
第一次执行 select * from pubs..authors where au_id='172-32-1176' 语句时只获取页面的意向锁。
而执行 update pubs..authors set address='Ad. '+address where au_id='172-32-1176' 语句后，再执行 select * from pubs..authors where au_id='172-32-1176' 语句会获取索引码的共享锁。
这可能与 SQL SERVER 的查询优化方案有关。
csdyyr 2009-08-13
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 xman_78tom 的回复:]
在 sql 2005 中发现一个很有趣的问题。在 managerment studio 中第一次执行 9 楼的语句不能成功的阻塞会话二，而在会话一中执行下列语句后再执行 9 楼的语句便可以成功的阻塞会话二。
begin tran
update pubs..authors set address='Ad. '+address where au_id='172-32-1176'
rollback
我想这可能与 managerment studio 或 .net 有关,正在深入研究中。


[/Quote]
关注研究成果。
csdyyr 2009-08-13
  • 打赏
  • 举报
 回复
[Quote=引用 24 楼 zuzuou 的回复:]
用select * from a for update
语句就能把查出来的记录锁住
[/Quote]
SQL 没这个用法。
csdyyr 2009-08-13
  • 打赏
  • 举报
 回复
[Quote=引用 22 楼 zwzw911 的回复:]
LZ搞错了。
在默认的read commit的隔离级别，当使用select对某个记录进行读取的时候，在这个记录上加的是共享锁，当记录读取完毕，共享锁立即释放。而共享锁的作用，只是防止其它进程中的事务，对当前的记录进行修改，而读取是不受限制的。
9楼的例子就很好的说明了这点，即使一个没有结束的事务通过select对记录添加了共享锁，但是第二个事务中的select还是能够读取这个记录。
而当使用update进行记录更新的时候，会使用到两个锁，首先是共享锁，这个锁发生在查找记录的时候（要更新记录就得先找到记录），然后进行更新的收候，对记录添加排他锁，当添加了排他锁之后，其它进程中/事务中的SQL就无法对记录进行读取和更新了。
将9楼的第一个事务中的select改成update，然后执行第二个事务，就可以看到第二个事务被阻塞了
[/Quote]
你说的都对，不过我就想用select也会阻塞第二个事务。
如果这样用会达到目的：select * from authors with (xlock,paglock) where au_id='172-32-1176' ,
但这样不仅仅锁住一条，其它的也被锁了。
zuzuou 2009-08-13
  • 打赏
  • 举报
 回复
用select * from a for update
语句就能把查出来的记录锁住
zwzw911 2009-08-13
  • 打赏
  • 举报
 回复
补充，
共享锁：其它事务可以读取，但是不能写入（更改）
更新锁：可以看成是共享锁和排他锁的混合
排他锁：当记录上有排他锁之后，其它事务无法对记录进行读取或者写入。
还有意向锁（不过还没有搞的很明白）
zwzw911 2009-08-13
  • 打赏
  • 举报
 回复
LZ搞错了。
在默认的read commit的隔离级别，当使用select对某个记录进行读取的时候，在这个记录上加的是共享锁，当记录读取完毕，共享锁立即释放。而共享锁的作用，只是防止其它进程中的事务，对当前的记录进行修改，而读取是不受限制的。
9楼的例子就很好的说明了这点，即使一个没有结束的事务通过select对记录添加了共享锁，但是第二个事务中的select还是能够读取这个记录。
而当使用update进行记录更新的时候，会使用到两个锁，首先是共享锁，这个锁发生在查找记录的时候（要更新记录就得先找到记录），然后进行更新的收候，对记录添加排他锁，当添加了排他锁之后，其它进程中/事务中的SQL就无法对记录进行读取和更新了。
将9楼的第一个事务中的select改成update，然后执行第二个事务，就可以看到第二个事务被阻塞了
chowyi 2009-08-13
  • 打赏
  • 举报
 回复
所谓锁 只是一个标识
flyfly2008 2009-08-13
  • 打赏
  • 举报
 回复
学习
slund 2009-08-13
  • 打赏
  • 举报
 回复
学习
w11x22b33 2009-08-13
  • 打赏
  • 举报
 回复
花10块钱去买把锁
加载更多回复（21）
insert into select语句锁表故障
前几天，一个mysql数据库运维同事，在生产上用insert into select * from语句，在生产上备份了一张表，结果将备份表全表锁住了，业务影响了大约10分钟。 看到这个语句，我第一反应就是select语句也能锁表，可是生产...
关于用select-for-update语句产生锁住记录不让其他事务(线程)修改的问题
背景 我们知道update库表里的一条数据会...除了update 语句，select for update 语句也是可以产生行锁的，可以用于临时锁定一行记录不被修改。 今天研究的课题就是研究下 select for update 的细节 代码 直接上代码 Re
SELECT语句中的for update的用法(锁的运用)
回复1：一般FOR UPDATE用在PL/SQL的游标里，它的作用就是一个行级锁（对游标里所有的记录），如果其他进程要更新这个游标行级锁里的记录，就必须等待当前进程的COMMIT或者回滚。 该语句用来锁定特定的行（如果有...
select......for update会锁表还是锁行？
select.....for update 会锁表还是会锁行
mysql执行一条语句会加锁吗_Mysql 执行 select 查询语句的时候 会自动加锁吗?
或者说有没有什么文档是介绍 Mysql 查询时内部的锁的机制 (不是手动加锁)更新一下问题, 举例子:myisam 如果先在 session1 中执行耗时长的 select 查询 (非事务), 然后 session2 中执行 update 这个被查询的表的数据,...
MS-SQL Server

34,593

社区成员

254,590

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章