三种事务的比较

来源：互联网发布：windows系统分级编辑：程序博客网时间：2024/05/17 21:55

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();

}