SQL开发人员中是否有表对象的锁定机制？ [英] Is there any locking mechanisms for table object in SQL developer?

问题描述

在我的多用户数据库中，多个并发事务中的语句可以更新表中的相同数据。我必须限制数据的访问，直到一个交易完成



我尝试过：



我听说过Data Concurrency&一致但无法理解

In my multiuser database, the statements within multiple simultaneous transactions can update the same data in a table. I have to restrict access of data until one transactions gets complete

What I have tried:

I had heard about Data Concurrency & Consistency but could not understand

推荐答案

您需要这样做非常罕见，但至少有几个选项。



1）用 TABLOCKX 锁定整个表格：

It's extremely rare that you would need to do that, but there are at least a couple of options.

1) Lock the entire table with TABLOCKX:

sql server - TABLOCK vs TABLOCKX - Stack Overflow [ ^ ]：

如果您在一个交易中并且在桌面上获取了一个独占锁，那么EG：

If you are in a transaction and you grab an exclusive lock on a table, EG:

SELECT 1 FROM TABLE WITH (TABLOCKX)

没有其他进程能够获取表上的任何锁，这意味着所有查询都试图与表将被阻塞，直到事务提交。

No other processes will be able to grab any locks on the table, meaning all queries attempting to talk to the table will be blocked until the transaction commits.

2）使用手动应用程序锁：

sp_getapplock（Transact-SQL）| Microsoft Docs [ ^ ]

sp_releaseapplock（Transact-SQL）| Microsoft Docs [ ^ ]

此 WITH（ NOLOCK）示例 [ ^ ]应该给你关于如何锁定表格的想法。

This WITH (NOLOCK) example[^] should give you an idea about how to go for locking the tables.

这篇关于SQL开发人员中是否有表对象的锁定机制？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！