sleeping awaiting command blocks other transactions

I have a big web application on asp.net 2.0. Usere open object editor there and makes some changes. They can not open the same object at one time.

After they press "save" btn all changes process to save on server via postback.

I am using transaction for save. There are a lot of procedures, checks and others to be done before saving operation is OK.

using (SqlConnection con = .........)
        {
            SqlTransaction trans = null;
            try
            {
                con.Open();
                trans=con.BeginTransaction(IsolationLevel.ReadUncommitted);
                ........operations.........
                trans.Commit();
            }
            catch (Exception e)
            {
                try { if (trans != null) trans.Rollback(); }
                catch { }
                throw new MyException("SQL Exception: " + e.Message, e);
            }
            finally
            {
                if (con != null && con.State == ConnectionState.Open) con.Close();
            }
        }

For me this code is quite safe.

But periodicaly happens: one process from this web application on saving operation on mssql became "sleeping/awaiting". and others p开发者_如何学Gorocesses called by other users became locked by this process and organiza a queue.

One of them threw timeout excetion.....but others are waiting.

So, my question is: does my code have some bad operation that allows command to became sleeping/awaiting? May be there are some tricks?

Since you are doing a lot DB of operations inside a transaction there is a chance that a database lock is blocking your application.

You can use the sp_who2 stored procedure (there are more details on it here) to see if there any blocks on your server, by checking the BlkBy column of the result.

You might also want to check the following links on Sql Server locks and deadlocking

Может ли это быть связано с репликацией. В момент когда наблюдаются описанные выше тормоза репликация начинает сильно тормозить. И пишет: The process could not connect to Publisher 'эта бд'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084) Get help: http://help/MSSQL_REPL20084

· TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. (Source: MSSQLServer, Error number: 10060) Get help: · An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (Source: MSSQLServer, Error number: 10060) Get help: · Login timeout expired (Source: MSSQLServer, Error number: 0) Get help: · The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQLServer, Error number: 0) Get help: Эта БД является источником репликации.

But the main problem is that the first process does not threw timeout exception. It is sleeping as log as we do not kill him. That is the main problem.