Pattern for simple table lock in SQL Server

Question

I've been reading the MSDN about transaction isolation levels and table hints in an effort to find out what I need to do to exclusively lock a table while I perform a 2 step insert in SQL Server. I've come up with 2 ways to do it and would like to know what the difference is between the approaches.

This answer shows how to do it with hints (https://stackoverflow.com/a/23759307/545430):

--Enclose steps in transaction to define an atomic operation
BEGIN TRAN
    -- Perform an insert that locks tblMoo
    INSERT INTO tblMoo SET fldCow='valPie' WITH (TABLOCKX, SERIALIZABLE)
    UPDATE tblMoo SET fldCowIndex=(SELECT MAX(fldCowIndex) + 1)
COMMIT TRAN

I think I could also achieve my objective by setting the isolation level:

BEGIN TRAN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    INSERT blah blah blah
    UPDATE hoo dee dah
COMMIT TRAN

It is important that I lock the entire table from any updates while my transaction is inserting this new row. Would both approaches yield the same result: the table being locked for both the INSERT and UPDATE commands?

Possible duplicate of [SQL Server - How to lock a table until a stored procedure finishes](http://stackoverflow.com/questions/3662766/sql-server-how-to-lock-a-table-until-a-stored-procedure-finishes) — Mike Sherrill 'Cat Recall', Jun 09 '16 at 00:33
@Mike Sherrill - I would like an answer as to the difference between the 2 methods I've posted. I've edited my question to clarify this. — Ian, Jun 20 '16 at 23:11
`set transaction isolation level serializable` doesn't lock a table. — Mike Sherrill 'Cat Recall', Jun 21 '16 at 01:58
@Mike Sherrill - Ok, so then doing this: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; INSERT INTO tblMoo SET fldCow='valPie' WITH (TABLOCKX); Is the same as this: INSERT INTO tblMoo SET fldCow='valPie' WITH (TABLOCKX, SERIALIZABLE). Right? — Ian, Jun 21 '16 at 02:17

Pattern for simple table lock in SQL Server

0 Answers0