none
SET DEADLOCK_PRIORITY for a DataContext RRS feed

  • Question

  • Hi

    I would like to set the Deadlock Priority = -10 for my DataContext. How can I do that?

    dataContext. ??
    dataContext.MyEntities.DeleteAllOnSubmit(oldRecords);
    dataContext.SubmitChanges() // delete old records with low deadlock priority

    Thank you in advance

    Wednesday, April 15, 2009 1:59 PM

Answers

  • It will be executed on the current SQL connection.

    We don't create new connections once a DataContext is established so it should be valid for the lifetime of the DataContext.

    [)amien
    • Marked as answer by Dunken Wednesday, April 15, 2009 3:11 PM
    • Unmarked as answer by Dunken Thursday, April 16, 2009 9:07 AM
    • Marked as answer by Dunken Friday, April 17, 2009 3:24 PM
    Wednesday, April 15, 2009 2:51 PM
    Moderator
  • Yes, that should do it. Some additional decorations you may want (or not want) to use:

    using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope()) //new serializable transaction
    {
        using (SomeDataContext dc = new SomeDataContext()) //new datacontext
        {
            if (dc.Connection.State == ConnectionState.Closed) //if no explicit connection, attach one to the current DC.
            {
                dc.Connection.Open(); //open connection if not open
            }
            dc.ExecuteCommand("set deadlock_priority -10"); //lowest prio
            dc.SomeEntities.DeleteAllOnSubmit(oldEntities); //prepare for deletion
            dc.SubmitChanges(); //delete
            dc.ExecuteCommand("set deadlock_priority normal"); //normal prio
        }
        ts.Complete(); //commit the transaction
    }
    



    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com
    • Edited by KristoferAEditor Friday, April 17, 2009 2:56 PM added additional code sample
    • Marked as answer by Dunken Friday, April 17, 2009 3:24 PM
    Friday, April 17, 2009 2:43 PM
    Answerer

All replies

  • There is no specific support so you will need to execute the TSQL via the DataContext:

    db.ExecuteCommand("SET DEADLOCK_PRIORITY LOW");
    
    [)amien
    Wednesday, April 15, 2009 2:36 PM
    Moderator
  • Thank you Damian. How long is this statement valid? Until SubmitChanges? I thought it's just for one batch... and ExecuteCommand is a batch for itself, isn't it?

    Wednesday, April 15, 2009 2:43 PM
  • It will be executed on the current SQL connection.

    We don't create new connections once a DataContext is established so it should be valid for the lifetime of the DataContext.

    [)amien
    • Marked as answer by Dunken Wednesday, April 15, 2009 3:11 PM
    • Unmarked as answer by Dunken Thursday, April 16, 2009 9:07 AM
    • Marked as answer by Dunken Friday, April 17, 2009 3:24 PM
    Wednesday, April 15, 2009 2:51 PM
    Moderator
  • It will be executed on the current SQL connection.

    We don't create new connections once a DataContext is established so it should be valid for the lifetime of the DataContext.

    [)amien

    Correct me if I'm wrong, but doesn't that depend on which datacontext constructor is used?

    I thought that if a connection is passed to the DC constructor then the DC sticks to that connection, but if using the default constructor (...=new XYZDataContext() ) connections are created or taken from the pool as necessary and released back to the pool as necessary. I.e. the above is true if you pass a connection to the datacontext constructor but not if you let the DC handle connections, so if using ExecuteCommand to set any connection state then the dc must be created with a explicit connection passed in...(?)
    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com
    Wednesday, April 15, 2009 3:54 PM
    Answerer
  • Hi

    Can anyone solve this? It's very important for me.

    Thank you in advance
    Friday, April 17, 2009 6:47 AM
  • Damien's answer (to use ExecuteCommand) is correct.

    My remark was a follow-up just to clarify that you must ensure that all operations use the same connection as the call to ExecuteCommand if you use ExecuteCommand to set any connection-level state. This can be done by passing in a connection to the datacontext constructor or by calling the open method on the datacontext's connection property.

    Sorry if my reply caused any confusion.
    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com
    Friday, April 17, 2009 9:38 AM
    Answerer
  • Hi Kristofer

    Thank you for your comment. Therefore I can do something like this:

    dataContext.MyEntities.DeleteAllOnSubmit(oldRecords);
    dataContext.Connection.Open();
    dataContext.ExecuteCommand("SET DEADLOCK_PRIORITY LOW");
    dataContext.SubmitChanges();
    dataContext.Connection.Close();

    Friday, April 17, 2009 11:41 AM
  • Yes, that should do it. Some additional decorations you may want (or not want) to use:

    using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope()) //new serializable transaction
    {
        using (SomeDataContext dc = new SomeDataContext()) //new datacontext
        {
            if (dc.Connection.State == ConnectionState.Closed) //if no explicit connection, attach one to the current DC.
            {
                dc.Connection.Open(); //open connection if not open
            }
            dc.ExecuteCommand("set deadlock_priority -10"); //lowest prio
            dc.SomeEntities.DeleteAllOnSubmit(oldEntities); //prepare for deletion
            dc.SubmitChanges(); //delete
            dc.ExecuteCommand("set deadlock_priority normal"); //normal prio
        }
        ts.Complete(); //commit the transaction
    }
    



    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com
    • Edited by KristoferAEditor Friday, April 17, 2009 2:56 PM added additional code sample
    • Marked as answer by Dunken Friday, April 17, 2009 3:24 PM
    Friday, April 17, 2009 2:43 PM
    Answerer