22,209
社区成员
发帖
与我相关
我的任务
分享数据库加锁的问题
我一个表,大概是以下的简化结构
列名 f1 f2
________________
0 0
1 1
2 0
行数比较多,现在要执行的操作是读取f2=0的最小的f1记录,然后修改f2=1
请问怎么加锁效率最高
列名 f1 f2
________________
0 0
1 1
2 0
行数比较多,现在要执行的操作是读取f2=0的最小的f1记录,然后修改f2=1
请问怎么加锁效率最高
...全文
请发表友善的回复…
发表回复
吉普赛的歌 2018-11-06
- 打赏
- 举报
你只有一条， 那就是行锁。
不过， 建议你按我 #15 那样， 建立索引，并明确指定行锁。
jadetiger 2018-11-06
- 打赏
- 举报
这个不可行的,实际上我是简化了表的,更新的时候,还会更新其它字段,你这样可能更新很多次[/quote]
更新很多次是什么情况？[/quote]
多个人都可能查到f2=0的记录啊[/quote]
不会的，你可以试试，开两个窗口
一个窗口执行这个：
begin tran
UPDATE 表 SET f2=1 WHERE f2=0
waitfor delay '00:00:10' --延迟10秒测试用
commit tran SELECT * FROM 表 WHERE f2=0
begin tran
SELECT * FROM 表 WITH (UPDLOCK) WHERE f2=0
.........
UPDATE 表 SET f2=1 WHERE f2=0
commit tran 吉普赛的歌 2018-11-06
- 打赏
- 举报
1. 创建测试表及测试数据, 加索引
你可以参照的， 是加上一个索引， 这个非常重要
2. 更新时加事务：
这样处理之后， 一个在更新， 其它的更新自动跳过了正在更新的加锁行， 避免了冲突。
3. 为了避免阻塞， 这个表上的纯查询建议加 with(nolock)， 类似：
你可以参照的， 是加上一个索引， 这个非常重要
--在 tempdb 创建测试表及测试数据
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
f1 INT PRIMARY KEY,
f2 INT NOT NULL
)
GO
--插入 1000*1000 = 100 万 条数据
;WITH cte AS (
SELECT 1 AS c FROM [master].dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 1 AND 1000
)
,cte2 AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rid FROM cte AS a CROSS APPLY cte AS b
)
INSERT INTO t(f1,f2)
SELECT rid,rid%5+1 FROM cte2
--设置 20 条为 0 的数据作为更新测试用
UPDATE TOP (20) t SET f2=0 WHERE f1 % 5 = 0
--增加过滤索引
CREATE INDEX ix_t_f2 ON t(f2) WHERE f2=02. 更新时加事务：
BEGIN TRAN
SELECT top 1 f1,f2 FROM t WITH(ROWLOCK,XLOCK,READPAST)
WHERE f2=0 order by f1
UPDATE t SET f2=1 WHERE f2=0
COMMIT TRAN这样处理之后， 一个在更新， 其它的更新自动跳过了正在更新的加锁行， 避免了冲突。
3. 为了避免阻塞， 这个表上的纯查询建议加 with(nolock)， 类似：
select top 10 * from t with(nolock)
二月十六 2018-11-06
- 打赏
- 举报
这个不可行的,实际上我是简化了表的,更新的时候,还会更新其它字段,你这样可能更新很多次[/quote]
更新很多次是什么情况？[/quote]
多个人都可能查到f2=0的记录啊[/quote]
不会的，你可以试试，开两个窗口
一个窗口执行这个：
begin tran
UPDATE 表 SET f2=1 WHERE f2=0
waitfor delay '00:00:10' --延迟10秒测试用
commit tran 另一个窗口
SELECT * FROM 表 WHERE f2=0
后边这个窗口的语句是无法读取到上边更新语句的哪条数据的
[/quote]
你这个效率未必高啊,你看我的提问
你这个解决方法比如可能是10个人select成功了,但是在最后一步执行update的时候发现影响行数为0,那有9个人都要回滚[/quote]
不要让10个人都select成功，只让一个人select到数据，加一个UPDLOCK。
begin tran
SELECT * FROM 表 WITH (UPDLOCK) WHERE f2=0
.........
UPDATE 表 SET f2=1 WHERE f2=0
commit tran jadetiger 2018-11-06
- 打赏
- 举报
我想的有两种方法,
一种就是锁表
一种就是锁行,失败了的客户轮循再查询
不知道有没有更好的解决方法
jadetiger 2018-11-06
- 打赏
- 举报
f2有多个值,0表示未被处理的,其它的有其它含,他更新这个表只会更新这一条记录,但是还会更新相关的其它表的记录,一次更新的记录数量不多
吉普赛的歌 2018-11-06
- 打赏
- 举报
这个楼主有看么？
jadetiger 2018-11-06
- 打赏
- 举报
这个不可行的,实际上我是简化了表的,更新的时候,还会更新其它字段,你这样可能更新很多次[/quote]
更新很多次是什么情况？[/quote]
多个人都可能查到f2=0的记录啊[/quote]
不会的，你可以试试，开两个窗口
一个窗口执行这个：
begin tran
UPDATE 表 SET f2=1 WHERE f2=0
waitfor delay '00:00:10' --延迟10秒测试用
commit tran SELECT * FROM 表 WHERE f2=0
二月十六 2018-11-06
- 打赏
- 举报
这个不可行的,实际上我是简化了表的,更新的时候,还会更新其它字段,你这样可能更新很多次[/quote]
更新很多次是什么情况？[/quote]
多个人都可能查到f2=0的记录啊[/quote]
不会的，你可以试试，开两个窗口
一个窗口执行这个：
begin tran
UPDATE 表 SET f2=1 WHERE f2=0
waitfor delay '00:00:10' --延迟10秒测试用
commit tran 另一个窗口
SELECT * FROM 表 WHERE f2=0
后边这个窗口的语句是无法读取到上边更新语句的哪条数据的
jadetiger 2018-11-06
- 打赏
- 举报
这个不可行的,实际上我是简化了表的,更新的时候,还会更新其它字段,你这样可能更新很多次[/quote]
更新很多次是什么情况？[/quote]
多个人都可能查到f2=0的记录啊
吉普赛的歌 2018-11-06
- 打赏
- 举报
f2 只有 0， 1 两种值吗？
一次大约会更新多少行记录？
jadetiger 2018-11-06
- 打赏
- 举报
主键是f1,上千万行,修改的时候还会修改其它字段,我简化了,主要想表达的意思就是我要在表中找出一个f2=0的记录,操作完成设置f2=1
二月十六 2018-11-06
- 打赏
- 举报
这个不可行的,实际上我是简化了表的,更新的时候,还会更新其它字段,你这样可能更新很多次[/quote]
更新很多次是什么情况？
jadetiger 2018-11-06
- 打赏
- 举报
这个不可行的,实际上我是简化了表的,更新的时候,还会更新其它字段,你这样可能更新很多次
jadetiger 2018-11-06
- 打赏
- 举报
主键是f1,上千万行
二月十六 2018-11-06
- 打赏
- 举报
begin tran
UPDATE 表 SET f2=1 WHERE .......
commit tran
吉普赛的歌 2018-11-06
- 打赏
- 举报
1. 你这个表， 主键都没有吗？
2. 行数较多？到底有多少行？
二月十六 2018-11-06
- 打赏
- 举报
对的，会锁上表，yenange写的那个可以只锁行吗？试了一下好像也不行，之前没注意过这个东西，楼主现在实现了你的需求了吗？
[/quote]
你的不同会话之间会阻塞， 我的不会。
我上面有测试数据，你可以试下[/quote]
好吧，可能之前测试的有点问题，用你的数据语句试了一下，是对的，
吉普赛的歌 2018-11-06
- 打赏
- 举报
对的，会锁上表，yenange写的那个可以只锁行吗？试了一下好像也不行，之前没注意过这个东西，楼主现在实现了你的需求了吗？
[/quote]
你的不同会话之间会阻塞， 我的不会。
我上面有测试数据，你可以试下
二月十六 2018-11-06
- 打赏
- 举报
对的，会锁上表，yenange写的那个可以只锁行吗？试了一下好像也不行，之前没注意过这个东西，楼主现在实现了你的需求了吗？
