How to lock sql server table?

xch2005 · Post by **xch2005** » Mon Apr 26, 2010 6:21 am

Hi,

Basically we need to insert data into sql server table with lock enable so that no sidu operation is allowed for other users.

I have tried having isolation level to serializable, using hint as TABLOCKX but when the job is under execution in DataStage, I am able to select records from front end client.

I have tried this with ODBC and DRS stages but result not as expected. I am able to select records in another session which should not happen until DS job completes.

Is there any way to do this (ie. need to lock the table exclusively and do the insert in DataStage job)?

Thanks in advance.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 26, 2010 6:35 am

Use the transaction isolation level property.

xch2005 · Post by **xch2005** » Wed Apr 28, 2010 12:57 am

isolation level has been set to serializable
hint used is TABLOCKX

chulett · Post by **chulett** » Wed Apr 28, 2010 6:42 am

I'm assuming your "hint" is being used via user-defined (custom) SQL, yes? You may want to have your DBA help you, found this online as a possible issue for it not working:

The table hints are ignored if the table is not accessed by the query plan. This may be caused by the optimizer choosing not to access the table at all, or because an indexed view is accessed instead. In the latter case, accessing an indexed view can be prevented by using the OPTION (EXPAND VIEWS) query hint.