February 19, 2002 at 8:23 pm
What I am trying to do is to create a lock-scenario in a stored procedure using a cursor.
Stored Procedure looks something like below:
create procedure proc_name (@par1 varchar(20), par_out varchar(25) output) as
declare xx cursor for
Select col_1,col_2, col3 from some_table
where col_1 = @some_var
order by col_2 ;
Perform loop_test
fetch xx into ....;
update some_table set col_3 = @some_other_var where some_conditions;
Loop
Close xx ;
end
All I need to do is to lock those specific rows/better still if it's one particular row. This row would also be selected as part of the cursor.
The closest I could come to is using transaction isolation level as read committed and making the cursor as static.
But while from the Query Analyzer it seems to work : without a commit in the procedure the second connection-execution waits indefinitely.
But it does not work from the application.
Any simpler ways or proven methods of doing this ?
tia,
Kaushik.
February 20, 2002 at 9:54 am
If you want to "lock" a row, or series of rows in SQL Server so only one edit at a time can occur, I'd use a flag instead. It isn't full proof and every app that connects to teh db must implement a check, but it works.
Steve Jones
February 20, 2002 at 10:18 am
use the SCROLL_LOCKS option while declaring the cursor , this
creates a lock on the current record when the cursor is being processed
Hope this helps
February 20, 2002 at 11:20 am
How about adding a locking hint to the select?
Andy
February 21, 2002 at 10:57 pm
Managed to solve it using a static cursor and using using a XLOCK locking hint for the select.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply