Row level locking in a sp from an app

[timscronin]

I have a user app with a stored proc that maybe called and run upto 8 times in a row on the save of a form(don't preach, I know this is bad), and there coiuld be several users saving this form at the same time. Is there a way in the form to force row-level locking inside the sp in SQL 2000. I am getting deadlocks when they test this. The sp has one begin and commit inside of it

 

[sguslan]

Encapsulate all transactions within stored procedures, including both the BEGIN TRANSACTION and COMMIT TRANSACTION statements in the procedure. This provides two benefits that help to reduce blocking locks. First, it limits the client application and SQL Server to communications before and after when the transaction runs, thus forcing any messages between them to occur at a time other than when the transaction is running (reducing transaction time). Second, It prevents the user from leaving an open transaction (holding locks open) because the stored procedure forces any transactions that it starts to complete or abort.

Also you can with (rowlock) hints in your update.

________________________________________________________________________________
If you do not like change, get out of the IT business...