TABLOCKX 与可序列化

TABLOCKX versus SERIALIZABLE(TABLOCKX 与可序列化)
本文介绍了TABLOCKX 与可序列化的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一系列需要以原子方式运行的 T-SQL 查询.(见下文)...目的是允许 1 个用户一次检索一个唯一的行,并防止其他用户同时检索同一行.

I have a series of T-SQL queries that I need to run atomically. (See Below)... The purpose is to allow 1 user to retrieve a single, unique row at a time and prevent other users from retrieving the same row simultaneously.

到目前为止,我已经看到了两种可能的解决方案.1) 表提示 (HOLDLOCK, TABLOCKX) 和 2) 事务隔离级别 (SERIALIZABLE)...

So far I have seen two possible solutions. 1) Table Hints (HOLDLOCK, TABLOCKX) and 2) Transaction Isolation Level (SERIALIZABLE)...

我的问题:

  1. 哪个选项更好?

  1. Which option is better?

还有其他/更好的解决方案吗?

Is there another/better solution?

DECLARE @recordId int;

SELECT @recordId = MIN([id])
FROM Exceptions
WHERE [status] = 'READY';

UPDATE Exceptions
SET [status] = 'PROCESSING',
    [username] = @Username
WHERE [id] = @recordId;

SELECT *
FROM Exceptions
WHERE [id] = @recordId;

推荐答案

在这种情况下,

  • HOLDLOCK = SERIALIZABLE = 持续时间,并发
  • TABLOCKX = 独占表锁

这两个概念是不同的,你想要的也不是.

The 2 concepts are different and neither does what you want.

做你想做的事,避免竞争条件,需要强制一个非阻塞(READPAST)排他(UPDLOCK)行级(ROWLOCK)锁,.您还可以使用 OUTPUT 子句使其成为具有原子性的单个语句.这可以很好地扩展.

To do what you want, to avoid race conditions, you need to force a non-blocking (READPAST) exclusive (UPDLOCK) row level (ROWLOCK) lock,. You can also use the OUTPUT clause to make it a single statement that will be atomic. This scales well.

UPDATE
    E
SET
   [status] = 'PROCESSING', [username] = @Username
OUTPUT
   INSERTED.*
FROM
   (
    SELECT TOP 1 id, [status], [username]
    FROM Exceptions (ROWLOCK, READPAST, UPDLOCK)
    WHERE [status] = 'READY'
    ORDER BY id
   ) E

总的来说,锁有3个方面

In general, locks have 3 aspects

  • 粒度 = 锁定的内容 = 行、页、表(PAGLOCK、ROWLOCK、TABLOCK)
  • 隔离级别 = 锁定持续时间、并发性(HOLDLOCK、READCOMMITTED、REPEATABLEREAD、SERIALIZABLE)
  • 模式 = 共享/排他性 (UPDLOCK, XLOCK)

  • 组合"例如 NOLOCK, TABLOCKX

这篇关于TABLOCKX 与可序列化的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Execute complex raw SQL query in EF6(在EF6中执行复杂的原始SQL查询)
Hibernate reactive No Vert.x context active in aws rds(AWS RDS中的休眠反应性非Vert.x上下文处于活动状态)
Bulk insert with mysql2 and NodeJs throws 500(使用mysql2和NodeJS的大容量插入抛出500)
Flask + PyMySQL giving error no attribute #39;settimeout#39;(FlASK+PyMySQL给出错误,没有属性#39;setTimeout#39;)
auto_increment column for a group of rows?(一组行的AUTO_INCREMENT列?)
Sort by ID DESC(按ID代码排序)