Problem with deadlock on SELECT/UPDATE

Answer 1

Its not enough to have a serializable transaction you need to hint on the locking for this to work.

The serializable isolation level will still usually acquire the "weakest" type of lock it can which ensures the serializable conditions are met (repeatable reads, no phantom rows etc)

So, you are grabbing a shared lock on your table which you are later (in your serializable transaction) trying to upgrade to an update lock. The upgrade will fail if another thread is holding the shared lock (it will work if no body else it holding a shared lock).

You probably want to change it to the following:

SELECT * FROM SessionTest with (updlock) WHERE SessionId = @SessionId

That will ensure an update lock is acquired when the SELECT is performed (so you will not need to upgrade the lock).