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

Question

Hello, 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?

Answer 1

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.

Answer 2

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.