locked
Deadlock RRS feed

  • Question

  • I have a simple "select statement" caused deadlock. I am very sure my Linq To SQL statement is outside of a transaction, but the error reported back is as following. I am just surprised to see a single select statement against a single table could cause deadlock problem, needless to say it's outside of a transaction.

    Does Inq to sql put select statement inside of a transaction automatically?

    Thanks.

    - Disaptch failed!
    System.Data.SqlClient.SqlException: Transaction (Process ID 70) was deadlocked o
    n lock resources with another process and has been chosen as the deadlock victim
    . Rerun the transaction.
       at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo
     queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs
    , ICompiledSubQuery[] subQueries, Object lastResult)
       at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryI
    nfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompile
    dSubQuery[] subQueries)
       at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider
    .Execute(Expression query)
       at System.Data.Linq.Table`1.System.Linq.IQueryProvider.Execute[TResult](Expre
    ssion expression)
       at System.Linq.Queryable.Any[TSource](IQueryable`1 source, Expression`1 predi
    cate)
    called from my own code
    Wednesday, October 7, 2009 6:51 PM

Answers

  • Simple read queries can indeed run into dead lock situations with other running batches. As an example, if you have a read query that read from table X in one order, and another process that will update the same table in a different order then they can place their locks on the same rows or pages in different order; causing a deadlock situation. (Reading in SQL Server will by default place read locks, and will by default wait for locks)

    You may want to look into what else you have accessing the same table(s) as your query and whether anything can be changed to make them access resources in the same order (wherever possible). SQL Server's profiler also have a dead lock detection mode that can show what led up to a deadlock; it won't detect and diagnose all deadlocks but it can still be very useful in tracking down what caused a deadlock situation...

    In some situations it may be warranted to do dirty reads in read-only scenarios. You can achieve that by wrapping your read query in a System.Transactions.TransactionScope with IsolationLevel set to ReadUncommitted. But that is something that need to be judged from situation to situation; if used wherever there is no risk that the data you are getting can be pulled out or invalidated under your feet it can save a bit of server-side resources in addition to avoiding locks blocking queries...


    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by Yichun_Feng Wednesday, October 14, 2009 1:13 AM
    Thursday, October 8, 2009 3:58 AM
    Answerer

All replies

  • Your description proves the deadlock but doesn't give us any insight as to what's the cause.

    Keep in mind, when making modifications or querying the values - it doesn't happen instantaneously.  It's not until you submit or, in the scenario of querying, use the values of the result that the command executes.  Take this for example:

                TestLinqToSQLDataContext myTable = new TestLinqToSQLDataContext();
                var myColl = from t in myTable.tblExamples
                            select t;
    
                Console.WriteLine("Query Made");        // Place a break point here and while it's stopped - add a record to your table
    
                foreach (tblExample hold in myColl)
                    Console.WriteLine(hold.Col1);

    So in the above, if you add a record while the code is stopped on "Query Made," you'll see that record in the foreach iteration following it.  You may be experiencing a deadlock because you defined all of your enumerable collections and pointed them at what they should contain, but it's not until submission/use that the linq expression takes place.

    • Marked as answer by Yichun_Feng Wednesday, October 14, 2009 1:13 AM
    • Unmarked as answer by Alan_chen Friday, September 7, 2012 9:38 AM
    Wednesday, October 7, 2009 7:31 PM
  • Simple read queries can indeed run into dead lock situations with other running batches. As an example, if you have a read query that read from table X in one order, and another process that will update the same table in a different order then they can place their locks on the same rows or pages in different order; causing a deadlock situation. (Reading in SQL Server will by default place read locks, and will by default wait for locks)

    You may want to look into what else you have accessing the same table(s) as your query and whether anything can be changed to make them access resources in the same order (wherever possible). SQL Server's profiler also have a dead lock detection mode that can show what led up to a deadlock; it won't detect and diagnose all deadlocks but it can still be very useful in tracking down what caused a deadlock situation...

    In some situations it may be warranted to do dirty reads in read-only scenarios. You can achieve that by wrapping your read query in a System.Transactions.TransactionScope with IsolationLevel set to ReadUncommitted. But that is something that need to be judged from situation to situation; if used wherever there is no risk that the data you are getting can be pulled out or invalidated under your feet it can save a bit of server-side resources in addition to avoiding locks blocking queries...


    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by Yichun_Feng Wednesday, October 14, 2009 1:13 AM
    Thursday, October 8, 2009 3:58 AM
    Answerer
  • Hi syntax,

    After testing your demo, I am sure it doesn't work.

    'myColl' is Iqueryable, so it will excute command at foreach point.

    Have a nice day.


    Alan Chen
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, September 7, 2012 9:34 AM