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 m开发者_StackOverfloway 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?

---

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.

---

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.