how to detect sql server timeout from .NET application without using catch Exception

Question

Hi,

In my current application, i am performing an update by invoking T-SQL Update command. The problem is when the same record is locked by other users at that time.

At .NET application, the application will wait until SQL Server timeout, then it will throw the SqlException timeout.

Is it possible to perform a check first whether a particular record is locked by other process rather than catching the exception ?

Thanks,

hadi teo

Update : The SQL Server version used are 2000 and 2008

Answer 1

No, not really.

The standard way is to catch and handle SqlException Number 1205 (deadlock victim):

        switch ( sqlException.Number )
        {
            case -2: /* Client Timeout */ 
            case 701: /* Out of Memory */ 
            case 1204: /* Lock Issue */ 
            case 1205: /* Deadlock Victim */ 
            case 1222: /* Lock Request Timeout */ 
            case 8645: /* Timeout waiting for memory resource */ 
            case 8651: /* Low memory condition */ 
            ...
        }

Please see: How to catch SqlException caused by deadlock?

Most locking issues can be eliminated by providing the appropriate covering indexes.

Answer 2

You could use separate connection with very short timeout to attempt to lock the record by updating some field, but this is still not going to give you 100% reliability.

if you really have the situation with multiple users editing same records, you should look into optimistic locking techniques.

Also, make sure you do not allow users to lock the records at all - use disconnected mode for any updates. In other words, the locking will only occur for a short time of update (<100 ms)