Please help....

I have a Transfer table wich suppose to get every time there is a new job to be done. Then I will make a sp to get a job and set the job to be started. I have made the following code (That doesn't work):

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

BEGIN TRANSACTION Trans
GO
Declare @ID int
select Top 1 @ID = ID from transfer where startedtimestamp is null

WAITFOR DELAY '000:00:08'

update transfer set startedtimestamp = GetDate() WHERE ID = @ID and startedtimestamp is null
COMMIT TRANSACTION Trans
select @ID as ID1
GO

The problem is that I want to make sure that I either get a ID og null in return from this (I will read the data with that - not a problem ;) sp. I have more than one machine that's activating this sp and I want to make sure that only one machine gets the job that needs to bee done, hopefully by only locking the current record. The error occurs when execute the above code in to different connections in Query Analyzer, so I pretend to be to machine asking at the "same time". I know its something with my locking knowledge (read: the lack of it)

Please - any ideas is welcome.


Regards
~Kenneth Bune

What error are you getting? Or, what do you mean by "doesn't work"?

Also, I wouldn't use the "GO" after the BEGIN TRANSACTION statement.

Why are you holding the transaction open for 8 seconds? Is this testing code?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I'm getting this error:

Server: Msg 1205, Level 13, State 50, Line 11
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I run the code on to connections in the Query Analyzer thats why I use the wait so I'm sure I get a situation I want to test. (To computers asking for a task at the same time)

Your right about the GO, a rest of a desperat try... :-)

This is testing code. It's meent to be in a Stored Procedure (ex. sp_GetTask), and the sp, should also return a recordset with the data from a table that has a one-to-many relation with the task. But that is not a problem ;)

Please ask for more details if nesesary.



Regards
~Kenneth Bune

Locking is a complex subject. To really understand it, I suggest you get yourself a copy of Inside SQL Server 2000, by Kalen Delaney. In it she devotes an entire chapter to locking and how it works. There are also several chapters devoted to performance and query tuning which also deal with blocking and deadlock issues. I also understand she has, or is about to, come out with an entire book on the subject.

I can advance a theory as to why you are getting a deadlock. It is hard to know for sure without running your exact environment and analyzing the lock/block situation as it occurs. You can attempt to program around the deadlock, to insure it doesn't happen. You can also try a simpler approach and simply accept the fact that your processes may occasionally bump into one another. If a deadlock error occurs, simply trap the error, perhaps wait a moment or two and try again and allow the processes to sort themselves out by themselves.

Your transaction isolation level is serializable, which is the most restrictive level. When the first SELECT statement is executed, a shared lock is obtained on the set of rows where 'startedtimestamp' is null. Shared locks can be, er, shared, so multiple processes can all obtain locks on the same set of rows.

When the UPDATE is attempted, the process doing the update must promote the shared locks it holds to an exclusive lock. This can be done if the process holding the shared lock is the same as the one attempting the lock upgrade. If it is not, the process simply blocks, waiting for the lock to be freed by the other process(es) holding the shared lock. Meanwhile, eventually the second process then tries to do the UPDATE. It too must promote its shared lock. At this point, the lock manager notices that two processes are waiting on each other to free up a lock, so it declares a deadlock (this type of deadlock is called a conversion deadlock, as it results from the attempt to convert a shared lock to an exclusive lock). It picks one process to be the victim, and takes it out back and shoots it. :D The surviver can then proceed.

Your test situation is unrealistic, as you are holding the shared lock for an unreasonable period of time (8 seconds), thus virtually guaranteeing a deadlock will occur. Transactions must be kept as short as possible, to avoid these types of situations.

You might try using a lock hint on your SELECT statement so that it obtains an update lock on the row instead of a shared lock. An update lock is similar to an exclusive lock:
Any other process which attempts this SELECT will simply block waiting to obtain an update lock. The first process, having obtained the update lock earlier, can then proceed to execute the update and then commit its transaction, releasing the lock(s), allowing the second process to unblock and thus continue.

You probably should test to insure the update is not attempted when the ID returned from the SELECT is null (there are no rows with NULL 'startedtimestmap' values), and the WHERE clause in the UPDATE seems more than it needs to be. Why not simply UPDATE the row where the ID is the one obtained from the prior SELECT? Adding the test to 'startedtimestamp' broadens the range of the lock and is probably not necessary.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com