I am working on making an application run on both Postgres and SQL Server.
我正在努力在Postgres和SQL Server上运行应用程序。
in PostgreSQL you can do something like
在PostgreSQL中你可以做类似的事情
lock mytable exclusive;
which would keep the entire table from being written to(insert/updated). I need the entire table to be locked while certain things are updated(and RIDs can not be changed or else it'll screw it up)
这将使整个表不被写入(插入/更新)。我需要锁定整个表,同时更新某些内容(并且RID不能更改,否则它会搞砸)
I am not seeing any simple way to force Sql Server to do this though. The only things I'm seeing are for one query and are only "hints" which may or may not be followed. How can I lock a table so that it is read-only in SQL Server for the duration of a transaction?
我没有看到任何简单的方法来强制Sql Server执行此操作。我看到的唯一的东西是一个查询,只是“提示”可能会或可能不会遵循。如何锁定表以使其在事务期间在SQL Server中是只读的?
2 个解决方案
#1
3
A locked table is not read-only. Is locked.
锁定的表不是只读的。被锁住了。
SQL Server does not allow explicit locking of engine primitives (tables, partitions, pages, rows, metadata etc). It only allows you to aquire explicitly application locks via sp_getapplock.
SQL Server不允许显式锁定引擎基元(表,分区,页面,行,元数据等)。它只允许您通过sp_getapplock显式获取应用程序锁。
If you want to ensure correctness under concurency conditions, you're going to have to do it the way everyone else does it: via transactions, proper isolation level and correct update order. Many concurency race conditions can be avoided using the OUTPUT clause of UPDATE/DELETE/INSERT.
如果您想在可靠性条件下确保正确性,那么您将必须按照其他人的方式执行此操作:通过事务,适当的隔离级别和正确的更新顺序。使用UPDATE / DELETE / INSERT的OUTPUT子句可以避免许多可靠的竞争条件。
Ultimately you can place an X lock on a table with a SELECT ... FROM Table WITH (TABLOCKX) WHERE..., but I would call that extremly poor programing flair.
最终你可以在一个带有SELECT ... FROM TABLE WITH(TABLOCKX)WHERE ...的桌子上放置一个X锁,但我会称之为极差的编程风格。
#2
0
On SQL Server you can set the Transaction Isolation Level to be read commited or serializable for the relevant connection. That should ensure the locking you need.
在SQL Server上,您可以将事务隔离级别设置为对相关连接进行提交或可序列化。这应该确保您需要的锁定。
#1
3
A locked table is not read-only. Is locked.
锁定的表不是只读的。被锁住了。
SQL Server does not allow explicit locking of engine primitives (tables, partitions, pages, rows, metadata etc). It only allows you to aquire explicitly application locks via sp_getapplock.
SQL Server不允许显式锁定引擎基元(表,分区,页面,行,元数据等)。它只允许您通过sp_getapplock显式获取应用程序锁。
If you want to ensure correctness under concurency conditions, you're going to have to do it the way everyone else does it: via transactions, proper isolation level and correct update order. Many concurency race conditions can be avoided using the OUTPUT clause of UPDATE/DELETE/INSERT.
如果您想在可靠性条件下确保正确性,那么您将必须按照其他人的方式执行此操作:通过事务,适当的隔离级别和正确的更新顺序。使用UPDATE / DELETE / INSERT的OUTPUT子句可以避免许多可靠的竞争条件。
Ultimately you can place an X lock on a table with a SELECT ... FROM Table WITH (TABLOCKX) WHERE..., but I would call that extremly poor programing flair.
最终你可以在一个带有SELECT ... FROM TABLE WITH(TABLOCKX)WHERE ...的桌子上放置一个X锁,但我会称之为极差的编程风格。
#2
0
On SQL Server you can set the Transaction Isolation Level to be read commited or serializable for the relevant connection. That should ensure the locking you need.
在SQL Server上,您可以将事务隔离级别设置为对相关连接进行提交或可序列化。这应该确保您需要的锁定。