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:
SELECT ... FROM transfer WITH (UPDLOCK) WHERE ...
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.
Custom Apps, Inc.