WAITFOR DELAY '00:01'下面我要说的是一个谋杀了我的同事不少青春的问题。关于ADO.NET 和 sql server transaction.
听起来都不难。难么请看下面这段代码,然后回答后面的问题。
using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Test;Integrated Security=SSPI;"))
{
conn.Open();
string sql = "begin transaction t1 INSERT INTO [Test].[dbo].[Table_1] ([id]) VALUES (12) WAITFOR DELAY '00:01'; commit transaction t1 delete from [Test].[dbo].[Table_2] where id = 2";
SqlCommand comm = new SqlCommand(sql, conn);
comm.ExecuteNonQuery();
{
conn.Open();
string sql = "begin transaction t1 INSERT INTO [Test].[dbo].[Table_1] ([id]) VALUES (12) WAITFOR DELAY '00:01'; commit transaction t1 delete from [Test].[dbo].[Table_2] where id = 2";
SqlCommand comm = new SqlCommand(sql, conn);
comm.ExecuteNonQuery();
}
问题
- transaction t1 能否执行成功?
- 如果transaction t1 没有执行成功,t1是否会回滚,Table_1是否会被lock
- 位于 commit transaction t1 后面的代码是否会运行?
经过实验发现
- transaction 1 不能执行成功,因为SqlCommand.CommandTimeout 默认值是30秒。而代码 WAITFOR DELAY '00:01' 要求等待一分钟, ado.net就是抛出timeout expired exception.
- t1 不会回滚, Table_1将被永久lock ,此时我知道的唯一的答案就是重启sql server service.(不知道哪位大侠有没有其他办法?)
- t1 后面的代码不会执行,但是有人说会执行(不知道怎么回事)
后果如此严重,恐怖吧。
解决方案
使用ADO.NET中的事物,
Table_1 就不会被lock住了。
using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Test;Integrated Security=SSPI;"))
{
conn.Open();
SqlTransaction sqlTran = conn.BeginTransaction();
string sql = "begin transaction t1 INSERT INTO [Test].[dbo].[Table_1] ([id]) VALUES (12) WAITFOR DELAY '00:01'; commit transaction t1 delete from [Test].[dbo].[Table_2] where id = 2";
SqlCommand comm = new SqlCommand(sql, conn);
comm.Transaction = sqlTran;
try
{
comm.ExecuteNonQuery();
sqlTran.Commit();
}
catch (Exception )
{
sqlTran.Rollback();
}
{
conn.Open();
SqlTransaction sqlTran = conn.BeginTransaction();
string sql = "begin transaction t1 INSERT INTO [Test].[dbo].[Table_1] ([id]) VALUES (12) WAITFOR DELAY '00:01'; commit transaction t1 delete from [Test].[dbo].[Table_2] where id = 2";
SqlCommand comm = new SqlCommand(sql, conn);
comm.Transaction = sqlTran;
try
{
comm.ExecuteNonQuery();
sqlTran.Commit();
}
catch (Exception )
{
sqlTran.Rollback();
}
}