Lock Table 1

[jestrada101]

Is there a way to lock a table from other users until I'm done using that particular table?

 

[MartinCroft]

Hi

I take it your doing an update or insert, you can use a hint (HOLDLOCK)

see BOL

Hold a shared lock until completion of the transaction instead of releasing the lock as soon as the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE.

 

[jestrada101]

Could you provide me an example?

Thanks

 

[MartinCroft]

Hi

the following

UPDATE countrys WITH (HOLDLOCK)
SET country = country


will set a hold lock on the table until the update has completed

you could use a BEGIN TRAN and withhold the COMMIT TRAN
or ROLLBACK TRAN

if start another session in query anlyser and attempt to
access the table E.G

Session A


BEGIN TRAN
UPDATE countrys WITH (HOLDLOCK)
SET country = country



Session B

Select * from countrys


You would not get the result set from the select displayed
to you either commited the transaction or rolled it back in session A.


COMMIT TRAN


if you added the hint (NOLOCK) to the select statement however it would read the data, this is however reading dirty data

hope this of help. BOL (books on line) has a lot of info on this information

 

[jestrada101]

Thanks! I'll work with this and see how it goes.

Thanks again
JE