Locking of a record in SQL Server SP

[trends]

Hi,

IF user1 is accessing a row ,this particular row should not be accessed by other users.Only when the lock is released (After user1 completes his processing with that row) that row should be accessible by others.The locking of a row has to be done at the selection of a particular row(Has to be done in select statement).

This is an SP level coding where commands between the begin trans and commit trans will only appear .The begin trans and commit trans functionalities are done by DLL's which cannot be changed.

[klintan]

I think you need to set transaction isolation level to repeatable read.

[trends]

Hi,

Thank you for the reply.But we cannot change the transaction isolation level since in is built in dll.is there any other way to achieve this.We can only change the sp code.

[Vaderman]

If you have access to the SQL, then you can add a TABLOCKX hint as part of your query, as in:

Select * from record (TABLOCKX) where cust_ID = @value;

Other than that, depending on what language you are using, I'd setup read and write events and possibly 'critical sections'.

Regards

John

[OLANDO4PAX]

HI, i am having problems updating picture i store in a sql 2000 server once there are more than 10 pictures stored.

[Vaderman]

You'll get a better response if you refrain from hijacking another person's thread.

Regards

John