none
The deadlock_pripority is always 10 for sync framework RRS feed

  • Question

  • Sometimes, deadlock happened with sync framework on my system,

    And the deadlock_priority is 10  for sync framework 2.1 by enable traceon 1222,

    I know that the sql server will determines the session to be a deadlock victime by evaluating the cost of undoing the transaction of the participating sessions, and it selects the onw with the least cost

    But when some dead lock happened, I want the sync framework as the deadlock victim

    I don't like the setting "set deadlock_priority low" for every store procedure for sync framework, _selectchang, _insert....

    Does anybody have any idea on it??

    Regards,

    Abby


    Get reply from social.microsoft

    Friday, April 13, 2012 8:40 AM

Answers

  • what im saying is in addition to the set isolation level command in there, add another command calling set deadlock_priority explicitly. as i have mentioned already, there is no sync framework property for you set the deadlock priority. so you have to fire the command itself during a sync session.

    • Marked as answer by Abby.Tang Friday, April 20, 2012 7:54 AM
    Friday, April 20, 2012 2:51 AM
    Moderator
  • override the base method,   

    protected override IDbTransaction CreateApplicationTransaction()
        {
            //The transaction will be used to apply changes to the database
            IDbTransaction trans = this.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
            //execute the command to set the "deadlock_priority" for sync part
            SqlCommand command = new SqlCommand("SET DEADLOCK_PRIORITY LOW", (SqlConnection)this.Connection, (SqlTransaction)trans);
            command.ExecuteNonQuery();
            return trans;
            //return base.CreateApplicationTransaction();
        }


    Get reply from social.microsoft

    • Marked as answer by Abby.Tang Thursday, May 3, 2012 1:21 AM
    Thursday, May 3, 2012 1:20 AM

All replies

  • there is no property in Sync Framework to allow you to set that.

    this is a wild shot and am not sure if it will work if you piggy back on the CreateEnumerationTransaction

    but try overriding the CreateEnumerationTransaction and set the deadlock priority from there...

    for an example, have a look at file OracleDbSyncProvider in the sample Oracle Sync Provider: http://code.msdn.microsoft.com/Database-Sync-Oracle-and-037fb083

    Friday, April 13, 2012 8:55 AM
    Moderator
  • Thanks,

    But how can I set the deadlock priority using sqlconnection?

    It seems that there is property about deadlock priority by using sqlconnection

    Regards,

    Abby


    Get reply from social.microsoft

    Friday, April 13, 2012 4:44 PM
  • am not sure there is a connection string property you can set for that... you will have to send the sql command to set it similar to the approach i posted above...
    Saturday, April 14, 2012 2:20 AM
    Moderator
  • It is to set the isolation level, and it seems that there is no property to set the deadlock_priority.

    Thanks,


    Get reply from social.microsoft

    Thursday, April 19, 2012 2:26 PM
  • what im saying is in addition to the set isolation level command in there, add another command calling set deadlock_priority explicitly. as i have mentioned already, there is no sync framework property for you set the deadlock priority. so you have to fire the command itself during a sync session.

    • Marked as answer by Abby.Tang Friday, April 20, 2012 7:54 AM
    Friday, April 20, 2012 2:51 AM
    Moderator
  • I will add a new command to set the deadlock priority

    Appreciate for your help.


    Get reply from social.microsoft

    Friday, April 20, 2012 7:55 AM
  • override the base method,   

    protected override IDbTransaction CreateApplicationTransaction()
        {
            //The transaction will be used to apply changes to the database
            IDbTransaction trans = this.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
            //execute the command to set the "deadlock_priority" for sync part
            SqlCommand command = new SqlCommand("SET DEADLOCK_PRIORITY LOW", (SqlConnection)this.Connection, (SqlTransaction)trans);
            command.ExecuteNonQuery();
            return trans;
            //return base.CreateApplicationTransaction();
        }


    Get reply from social.microsoft

    • Marked as answer by Abby.Tang Thursday, May 3, 2012 1:21 AM
    Thursday, May 3, 2012 1:20 AM