事务与分布式事务

事务是一种将活动涉及的所有操作作为一个不可分割的执行单元，所有操作只有在正常执行的情况下方能提交，不然集体回滚。（All or Nothing）

事务具有ACID四种属性：原子性、一致性、隔离性、持久性。

事务的处理方式存在很多种处理方式：

可以居于数据库的存储过程编写SQL事务，或通过ADO.NET、企业库均提供事务。

1、数据库存储过程事务：

 数据库结构：

CREATE TABLE [dbo].[Player_Account](
    [AccountIPlayer_Account_ID] [int] IDENTITY(1,1) NOT NULL,
    [PlayerID] [int] NOT NULL,
    [AccountName] [varchar](30) NOT NULL,
    [Password] [varchar](30) NOT NULL,
    [Balance] [decimal](18, 0) NOT NULL,
    [Status] [int] NOT NULL,
    [ActiveTime] [datetime] NOT NULL,
    [CreateTime] [datetime] NOT NULL,
    [Remark] [varchar](100) NULL,
    [ModifyTime] [datetime] NULL,
    [ModifyBy] [varchar](30) NULL,
 CONSTRAINT [PK_PLAYER_ACCOUNT] PRIMARY KEY CLUSTERED 
(
    [AccountIPlayer_Account_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Member_Account](
    [Member_Account_ID] [int] IDENTITY(1,1) NOT NULL,
    [MemberID] [int] NOT NULL,
    [AccountName] [varchar](30) NOT NULL,
    [Password] [varchar](30) NOT NULL,
    [Balance] [decimal](18, 0) NOT NULL,
    [Status] [int] NOT NULL,
    [ActiveTime] [datetime] NOT NULL,
    [CreateTime] [datetime] NOT NULL,
    [Remark] [varchar](100) NULL,
    [ModifyTime] [datetime] NULL,
    [ModifyBy] [varchar](30) NULL
) ON [PRIMARY]

两个账户的转账是一种复合操作，同时也涉及到事务操作。（当然如果涉及到信用额度、可用额度、状态将更加复杂）

 存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--账号转账
CREATE PROCEDURE [dbo].[Transfer2Player]  
    @AccountIPlayer_Account_ID INT =NULL,
    @Member_Account_ID INT =NULL,
    @Amount DECIMAL=0
AS
BEGIN
    IF NOT EXISTS(SELECT * FROM Player_Account pa WHERE pa.AccountIPlayer_Account_ID=@AccountIPlayer_Account_ID)
      BEGIN
          RAISERROR('AccountNotExists',16,1)
          RETURN 
      END
    IF NOT EXISTS(SELECT * FROM Member_Account ma WHERE ma.Member_Account_ID=@Member_Account_ID)
      BEGIN
          RAISERROR('AccountNotExists',16,1)
          RETURN 
      END
   IF NOT EXISTS(SELECT * FROM Member_Account ma WHERE ma.Member_Account_ID=@Member_Account_ID  AND ma.Balance<@Amount)
     BEGIN
         RAISERROR('NoEnoughAmount',16,1)
        RETURN
     END
   --开始事务
   BEGIN TRANSACTION
     UPDATE Member_Account
     SET Balance = @Amount WHERE Member_Account_ID =@Member_Account_ID 
     IF @@ERROR<>0
      BEGIN
          --出错回滚
          ROLLBACK TRANSACTION
      END
     UPDATE Player_Account
     SET Balance = @Amount WHERE AccountIPlayer_Account_ID =@AccountIPlayer_Account_ID
     IF @@ERROR<>0
      BEGIN
          ROLLBACK TRANSACTION
      END
    --提交事务
    COMMIT TRANSACTION
END
GO

 

2、ADO.Net 事务

在System.Data.SqlClient也提供了事务功能

 

 private const string connectionString = "server=.;database=Game-Pay2012;sa=sa;pwd=123456;";

        public void Tranfer2Player(int Player_Account_ID,int Member_Account_ID,decimal Amount)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connectionString].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                conn.Open();
                cmd.Transaction = conn.BeginTransaction(IsolationLevel.RepeatableRead, "Tranfer2PlayerTransaction");
                //充值到玩家
                cmd.CommandText = " UPDATE Player_Account SET Balance = @Amount WHERE AccountIPlayer_Account_ID =@AccountIPlayer_Account_ID";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                SqlParameter[] paras = new SqlParameter[]{
                    new SqlParameter("@AccountIPlayer_Account_ID",SqlDbType.Int,32),
                    new SqlParameter("@Amount",SqlDbType.Decimal,18)
                };
                paras[0].Value = Player_Account_ID;
                paras[1].Value = Amount;
                foreach (SqlParameter para in paras)
                {
                    cmd.Parameters.Add(para);
                }
                cmd.ExecuteNonQuery();
                //扣除账号金额
                cmd.CommandText = "UPDATE Member_Account SET Balance = @Amount WHERE Member_Account_ID =@Member_Account_ID ";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                SqlParameter[] paras2 = new SqlParameter[]{
                    new SqlParameter("@Member_Account_ID",SqlDbType.Int,32),
                    new SqlParameter("@Amount",SqlDbType.Decimal,18)
                };
                paras[0].Value = Member_Account_ID;
                paras[1].Value = Amount;
                foreach (SqlParameter para in paras)
                {
                    cmd.Parameters.Add(para);
                }
                cmd.ExecuteNonQuery();
                //提交事务
                cmd.Transaction.Commit();
            }
            catch (Exception)
            {
                //回滚事务
                cmd.Transaction.Rollback();
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

 

出来ADO.Net 像企业库、Linq to Sql也提供了相关的事务。

3、分布式事务

 

TransactionScope 此类在.Net Framework 2.0 版新增的。它为数据库提供了一个“轻量级”（区别于SqlTransaction）的事务。
 在通过 new 语句实例化 TransactionScope 时，事务管理器将确定要参与哪个事务。一经确定，此范围将始终参与该事务。此决策基于两个因素：是否存在环境事务以及构造函数中TransactionScopeOption 参数的值。环境事务是在其中执行您的代码的事务。通过调用 Transaction 类的 Current 静态属性可获取对环境事务的引用。
详细请看MSDN：
http://msdn.microsoft.com/zh-cn/library/system.transactions.transactionscope(v=VS.80).aspx

 

 

    public class TransactionClass
    {
        private const string strcon1 = "Data Source=192.168.1.36;Initial Catalog=TransactionDB;user id=sa;Password=XhTeam;";
  
        private const string strcon2 = "Data Source=192.168.1.36;Initial Catalog=Transaction2DB;user id=sa;Password=XhTeam;";
      
        private const string strcom1 = "INSERT INTO Account(name,password)VALUES('test222','testone')";

        private const string strcom2 = "INSERT INTO Logger(title,test)VALUES('testlog222','testonecontent')";

        public void CreateTransactionScope()
        {
            TransactionOptions tOps = new TransactionOptions();
            tOps.IsolationLevel = IsolationLevel.RepeatableRead;
            tOps.Timeout = new TimeSpan(0, 3, 0);
            try
            {
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew,tOps))
                {
                    using (SqlConnection con1 = new SqlConnection(strcon1))
                    {
                        con1.Open();
                        SqlCommand com1 = new SqlCommand(strcom1, con1);
                        com1.ExecuteNonQuery();
                    }

                    using (SqlConnection con2 = new SqlConnection(strcon2))
                    {
                        con2.Open();
                        SqlCommand com2 = new SqlCommand(strcom2, con2);
                        com2.ExecuteNonQuery();
                    }
                    scope.Complete();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("异常信息: " + ex.Message);
            }
        }
    }
}

TransactionScope 有三种模式：

TransactionScopeOptions：Required、RequiresNew、Suppress

Required 如果已经存在一个事务，那么这个事务范围将加入已有事务，否则创建自己的事务。

RequiresNew 事务范围将创建自己的事务

Suppress 如果处于当前事务范围，那么事务范围既不会加入范围事务，也不会创建自己的事务。当部分代码留在事务外部时，可以使用该项。