CODEWITHSUNDEEP
sql-serverpostgresqllocking
Earlz
Earlz

Reputation: 63905

how to force a lock to be obtained in SQL Server?

I am working on making an application run on both Postgres and SQL Server.

in PostgreSQL you can do something like

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)

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?

Upvotes: 1

Views: 4612

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294427

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.

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.

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.

Upvotes: 3

Oded
Oded

Reputation: 499352

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.

Upvotes: 0

Related Questions