数据库事务的隔离级别——实例

最近学习事务的隔离级别，看了“头发又乱了”http://www.cnblogs.com/wzcheng/archive/2006/10/18/532243.html的博客，写的很好，有同学想看代码，我就整理了一下，大家分享一下。

   模拟并发情况：

/// <summary>
    /// 模拟并发更新XX款
    /// </summary>
    public class TransWork
    {
        /// <summary>
        /// 静态变量——插入数据量
        /// </summary>
        public static int WorkCount;

        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="type">选择类型</param>
        public void Work(string type)
        {
            int departmentId = 0;
            int cost = 100;
            WorkCount = 1000;
            StringBuilder strSql = GetSql();
            IsolationLevel iso = IsolationLevel.ReadCommitted;
            switch (type)
            {
                case "1":
                    departmentId = 1;
                    iso = IsolationLevel.ReadCommitted;
                    break;
                case "2":
                    departmentId = 2;
                    iso = IsolationLevel.ReadUncommitted;
                    break;
                case "3":
                    iso = IsolationLevel.RepeatableRead;
                    departmentId = 3;
                    break;
                default:
                    Console.WriteLine("没有这个选项！");
                    break;
            }

            //循环线程，模拟并发情况，是否会发生事务线程锁死的情况
            while (WorkCount > 0)
            {
                Paras myPara = new Paras();
                myPara.Iso = iso;//数据库级别设置
                myPara.StrSql = strSql.ToString();//数据库语句
                if (DummyMethod.WorkCount % 2 == 0)
                    cost = -cost;//数据，这里只是为了方便检验结果设置的插入不同数据
                SqlParameter[] paras = GetParas(departmentId, cost);
                myPara.Para = paras;//参数集
                Thread thread = new Thread(new ParameterizedThreadStart(TransCommitted));//线程方法
                thread.Start(myPara);//启动线程
                WorkCount--;//静态变量减1
            }
            Console.WriteLine("结束");
        }

        /// <summary>
        /// 向数据库中插入数据
        /// </summary>
        /// <param name="obj"></param>
        private void TransCommitted(object obj)
        {
            //数据库连接字符串。。。
            string connStr = "server=192.168.1.64;database=Test;uid=betterDEV;pwd=better2008;Asynchronous Processing=true";
            SqlConnection connection = new SqlConnection(connStr);//数据库连接
            Paras paras = obj as Paras;
            SqlTransaction trans;//事务
            if (connection.State != ConnectionState.Open)
                connection.Open();//数据库连接打开
            trans = connection.BeginTransaction(paras.Iso);
            try
            {
                using (SqlCommand command = new SqlCommand(paras.StrSql, trans.Connection))
                {
                    command.Transaction = trans;
                    command.Parameters.Clear();//清除参数
                    command.Parameters.AddRange(paras.Para);//设置参数
                    command.ExecuteNonQuery();
                    trans.Commit();//事务的提交
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());//异常输出
                trans.Rollback();//事务回滚
            }
            finally
            {
                //数据库连接关闭
                connection.Close();
            }
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="departmentId">部门ID</param>
        /// <param name="cost">一笔消费</param>
        /// <returns>参数集</returns>
        private SqlParameter[] GetParas(int departmentId, int cost)
        {
            SqlParameter[] paras = new SqlParameter[] {
            new SqlParameter("@departmentID",SqlDbType.Int),
            new SqlParameter("@cost",SqlDbType.Int)
            };
            paras[0].Value = departmentId;
            paras[1].Value = cost;
            return paras;
        }

        /// <summary>
        /// 获取sql语句
        /// </summary>
        /// <returns>sql语句</returns>
        private StringBuilder GetSql()
        {

　　　　//这个语句你们可以根据自己的想象，主要包括读，更新操作
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" insert into TestTrans(DepartmentID,Cost) values(@departmentID,@cost) ");
            strSql.Append(" declare @ID int ");
            strSql.Append(" select  @ID=@@IDENTITY from TestTrans ");
            strSql.Append(" declare @balance decimal(18,0) ");
            strSql.Append("  select top (1)  @balance = balance from TestTrans where DepartmentID=@departmentID and ID<@ID order by ID desc  ");
            strSql.Append(" if(@balance is null) ");
            strSql.Append(" begin ");
            strSql.Append(" select @balance=0; ");
            strSql.Append(" end ");
            strSql.Append(" Update TestTrans set Balance=Cost+@balance where ID=@ID ");
            return strSql;
        }

    }

    /// <summary>
    /// 为线程传入的参数类
    /// </summary>
    public class Paras
    {
        /// <summary>
        /// sql语句中的参数集
        /// </summary>
        public SqlParameter[] Para { set; get; }

        /// <summary>
        /// sql语句
        /// </summary>
        public string StrSql { set; get; }

        /// <summary>
        /// 事务隔离级别
        /// </summary>
        public IsolationLevel Iso { get; set; }
    }
}