三种事务的比较

 using (TransactionScope ts = new TransactionScope())
{
     using (SqlConnection conn = new SqlConnection(@"Data Source=10.108.*.*;Initial Catalog=DATABASENAME;User ID=***;Password=***"))
     {
         conn.Open();

         //(一)最优雅的方式

         //SqlCommand sqlCommand = new SqlCommand("UPDATE ERP_DANJUSUFFIX SET SUFFIX=SUFFIX+1 WHERE STYLE='TEST'", conn);
         //sqlCommand.ExecuteNonQuery();

         //sqlCommand = new SqlCommand("SELECT SUFFIX FROM ERP_DANJUSUFFIX WHERE STYLE='TEST'", conn);
         //decimal suffix = (decimal)sqlCommand.ExecuteScalar();

 

         //(二)SELECT会读出相同数据，但Update会所住相应的资源，导致死锁，结果是一个进程读取的值是无效的：事务(进程 ID 64)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。

         //SqlCommand sqlCommand = new SqlCommand("SELECT SUFFIX FROM ERP_DANJUSUFFIX WHERE STYLE='TEST'", conn);
         //decimal suffix = (decimal)sqlCommand.ExecuteScalar();

        //sqlCommand = new SqlCommand("UPDATE ERP_DANJUSUFFIX SET SUFFIX=SUFFIX+1 WHERE STYLE='TEST'", conn);
        //sqlCommand.ExecuteNonQuery();

        

        //(三)锁住表的方式  

        SqlCommand sqlCommand = new SqlCommand("SELECT SUFFIX FROM ERP_DANJUSUFFIX with(tablockx) WHERE STYLE='TEST'  ", conn);
        decimal suffix = (decimal)sqlCommand.ExecuteScalar();

        sqlCommand = new SqlCommand("UPDATE ERP_DANJUSUFFIX SET SUFFIX=SUFFIX+1 WHERE STYLE='TEST'", conn);
        sqlCommand.ExecuteNonQuery();

        conn.Close();

    }
    ts.Complete();

}