I have a proc which has update query which is getting invoked in parallel. I have used Sybase select for update command to select set of rows so that transaction who has picked up these set of record should be responsible for updating and no other transaction should select and update the same record. But after using select for update I am getting following error:
"Your server command (family id #0, process id #416) encountered a deadlock situation. Please re-run your command."
Basically I am trying to invoke proc in parallel thread and one of thread is able to perform the update query and rest all thread fail giving above exception. Here is code snippet
begin transaction
declare x cursor for select a from B where c=1 for update of p
open x
fetch x into @N
while(@@sqlstatus != 2)
begin
update B set p=1
where a =@N
end
commit transaction
This is likely due to Table/All Pages locking level set for the table, which means that when a transaction starts, the process acquires locks for the whole table.
Your first process and second process are trying to acquire locks on the same resources, and that's causing the deadlock.
To change the locking on a serverwide basis, you can use:
sp_configure "lock scheme", 0, [ allpages | datapages | datarows ]
To change it on a per table basis
alter table <tablename>
lock [ allpages | datapages | datarows ]
Check the following sections of the Sybase Documentation for more information: