唯一且连续的编码的实现

    在信息系统的开发过程中，我们通常要处理各种各样的编码问题，有的教科书甚至将编码设计提升为系统设计阶段的一个重要步骤。此处所谓的编码，是“编号”的近义词，而非有时我们所说的“编写代码”，它通常作为对象的标识存储在数据库中。

    既然是标识，那么编码应当是唯一的，事实上，唯一性是比较容易实现的：自动编号类型的字段（如果支持的话）、一定精度的当前时间等等。此时，我们不考虑这种编码是否具备一定的含义（通常这是编码设计的一个原则），我们在唯一性基础之上考虑另外一种常见的需求：编码应当是连续的，即不重号且不断号。

    这种需求下的编码中无疑都存在一个由简单整数数字组成的部分，如下图所示。由于其它部分可以通过某种方式补齐，为了简化问题，我们可以假设要实现的编码只有该整数数字部分，即我们要实现的编码序列是{1, 2, 3, ..., 10, 11, 12, ...}。

    图1 唯一且连续的编码示例

    下面的篇幅将讨论在后台数据库为SQL Server 2000的情况下的解决方案和可能遇到的一些问题。

    假设SQL Server 2000中已经存在一个名为tEsTdB的数据库，执行以下脚本——    

代码1 创建用户表Invoice的SQL语句
 1if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice]') and 
 2
 3OBJECTPROPERTY(id, N'IsUserTable') = 1)
 4drop table [dbo].[Invoice]
 5GO
 6
 7CREATE TABLE [dbo].[Invoice] (
 8    [InvoiceID] [bigint] IDENTITY (1, 1) NOT NULL ,
 9    [InvoiceNo] [bigint] NOT NULL ,
10    [Type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
11    [Status] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
12) ON [PRIMARY]
13GO

    通过以上脚本，我们在tEsTdB数据库中获得一个名为Invoice的用户表，其中InvoiceID是一个自动编号的bigint型字段，而InvoiceNo是一个普通的bigint型字段。准备工作之后，我们要做的就是向Invoice表中新增记录，要求是对于InvoiceNo字段必须是唯一且连续的。

    对于有过开发经验的朋友来说，这是一个很稀松平常的任务，看看他是如何实现的：

代码2 向Invoice表中增加新记录
 1        private void btnInsert_Click(object sender, EventArgs e)
 2        {
 3            string strConn = @"Data Source=WAXDOLL\SQL2K;Initial Catalog=tEsTdB;Integrated 
 4
 5Security=True";
 6            string strSQL = @"
 7                    SELECT 
 8                        MAX(InvoiceNo) AS MaxNO
 9                    FROM Invoice
10                ";
11            System.Data.SqlClient.SqlConnection sconn =
12                new System.Data.SqlClient.SqlConnection(
13                strConn
14                );
15            sconn.Open();
16            System.Data.SqlClient.SqlCommand scomm =
17                new System.Data.SqlClient.SqlCommand(
18                strSQL, sconn
19                );
20            object objMaxNo = scomm.ExecuteScalar();
21            int intMaxNo = 0;
22            if (objMaxNo != null && objMaxNo != System.DBNull.Value)
23            {
24                intMaxNo = System.Convert.ToInt32(objMaxNo);
25            }
26            intMaxNo++;
27            this.lblCurrentNO.Text = intMaxNo.ToString();
28            scomm.CommandText = "INSERT INTO Invoice (InvoiceNo, [Type], Status) VALUES (" + 
29
30intMaxNo.ToString() + ", 'a', 'b')";
31            scomm.ExecuteNonQuery();
32            scomm.Dispose();
33            sconn.Close();
34            sconn.Dispose();
35        }

    OK，这段简单代码看起来运行不错，尤其是在一些基础数据维护时由于基础数据不需要经常变动（也许整个生命周期都不会变动一次，因为在数据准备阶段已经固定下来了，提供修改的接口只是为了软件的完整性），不会出现什么大问题。现在，来看看下面的情况——

    修改代码2让按钮单击事件中的代码执行10000次，然后将生成的exe文件复制10份并分别执行它们，如下图所示。

    图2 将SeqNo_IncorrectWay.exe复制10份并运行

    [下载代码2]

    分别单击这10个窗体上的按钮向Invoice表中插入数据，不用等到所有的操作都完成，大概差不多就可以了，在查询分析器中执行下面的语句：

代码3 检查InvoiceID和InvoiceNo是否相等
1SELECT * FROM Invoice WHERE InvoiceID <> InvoiceNo

    由于InvoiceID是自动编号类型的字段，在只有Insert操作的情况下，如果InvoiceNo是唯一且连续的，每一条记录的InvoiceID应该和InvoiceNo相同，当然，在执行Insert操作之前应当保证InvoiceID是从1开始的，最好在这之前使用语句TRUNCATE TABLE Invoice重新创建Invoice表。所以，如果代码3返回任何记录，则证明InvoiceNo不是唯一的，即出现了重号的情况。（如果是SQL Server 2005的话，我们甚至根本不需要InvoiceID这个字段，因为在SQL Server 2005中可以使用ROW_NUMBER()返回记录的行号，我的随笔RDL(C) Report Design Step by Step 3: Mail Label中有一个关于ROW_NUMBER()的应用）

    事实上，上面同时运行的10个窗体模拟了多个客户端的并发情况，但由于InvoiceNo字段并非不能重复，所以这又和常说的并发冲突有所区别，程序的执行没有问题，但是在逻辑上是错误的。也就是说，在多用户并发的情况下，这种实现无法满足需求。

    需要指出的是，上面的简单代码中，由于线程被阻塞，所以标签lblCurrentNO并不会显示当前插入的InvoiceNo，在稍后会给出一个多线程的可以显示当前插入的InvoiceNo的值的例子。

    接下来，我们看一下，是否可以通过调用下面的存储过程来达到我们的目的：

代码4 存储过程GetInvoiceNo
 1CREATE  PROCEDURE dbo.GetInvoiceNo
 2(
 3    @SeqNo BIGINT OUTPUT
 4)
 5AS
 6    BEGIN TRANSACTION
 7    UPDATE dbo.CurrentNo SET @SeqNo = CurrentNo = CurrentNo + 1
 8    INSERT INTO dbo.Invoice (InvoiceNo, Type, Status) VALUES (@SeqNo, 'a', 'b')
 9    COMMIT
10GO

    其中，表CurrentNo专门用于存储当前插入的InvoiceNo的值，这样可以带来的一个显而易见的好处是避免扫描Invoice表获取InvoiceNo的最大值，在记录数较大时提高效率。该表只有一个bigint型的字段CurrentNo，可以使用下面的语句创建该表：

代码5 创建用户表CurrentNo的SQL语句
 1if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CurrentNo]') and 
 2
 3OBJECTPROPERTY(id, N'IsUserTable') = 1)
 4drop table [dbo].[CurrentNo]
 5GO
 6
 7CREATE TABLE [dbo].[CurrentNo] (
 8    [CurrentNo] [bigint] NOT NULL 
 9) ON [PRIMARY]
10GO

    测试用窗体的界面如下图所示：

    图3 多线程测试用窗体

    窗体上的每个按钮单击后都创建一个一个新的线程向数据库中循环插入记录，同时避免由于线程阻塞无法显示当前插入的InvoiceNo的值，窗体代码如下：

代码6 窗体SeqNo_MultiThread.frmTestSeqNo
  1using System;
  2using System.Threading;
  3using System.Collections.Generic;
  4using System.ComponentModel;
  5using System.Data;
  6using System.Data.SqlClient;
  7using System.Drawing;
  8using System.Text;
  9using System.Windows.Forms;
 10
 11namespace SeqNo_MultiThread
 12{
 13    public partial class frmTestSeqNo : Form
 14    {
 15        public frmTestSeqNo()
 16        {
 17            InitializeComponent();
 18        }
 19
 20        private delegate void SetCaptionCallBack(string text, int no);
 21
 22        private Thread[] thInsert = new Thread[10];
 23
 24        private void ThreadSafeInsert(object no)
 25        {
 26            for (int i = 0; i < 10000; i++)
 27            {
 28                string strConn = @"Data Source=WAXDOLL\SQL2K;Initial 
 29
 30Catalog=tEsTdB;Integrated Security=True";
 31                SqlConnection sconn = new SqlConnection(strConn);
 32                sconn.Open();
 33                SqlCommand scomm = new SqlCommand("GetInvoiceNo", sconn);
 34                scomm.CommandType = CommandType.StoredProcedure;
 35                SqlParameter sp = new SqlParameter("@SeqNo", SqlDbType.BigInt);
 36                sp.Direction = ParameterDirection.Output;
 37                sp.Value = null;
 38                scomm.Parameters.Add(sp);
 39                scomm.ExecuteNonQuery();
 40                scomm.Dispose();
 41                sconn.Close();
 42                sconn.Dispose();
 43                this.SetCaption(sp.Value.ToString(), Convert.ToInt32(no));
 44                System.Threading.Thread.Sleep(100);
 45            }
 46        }
 47
 48        private void SetCaption(string text, int no)
 49        {
 50            Label lblCurrent = null;
 51            foreach( Control ctl in this.Controls )
 52            {
 53                if (ctl.Name == "lblCurrent" + no.ToString())
 54                {
 55                    lblCurrent = ctl as Label;
 56                    break;
 57                }
 58            }
 59            if (lblCurrent != null)
 60            {
 61                if (lblCurrent.InvokeRequired)
 62                {
 63                    SetCaptionCallBack d = new SetCaptionCallBack(SetCaption);
 64                    this.Invoke(d, new object[] { text, no });
 65                }
 66                else
 67                {
 68                    lblCurrent.Text = text;
 69                }
 70            }
 71        }
 72
 73        private void btnThread_Click(object sender, EventArgs e)
 74        {
 75            string strName = (sender as Button).Name;
 76            int intOrder = 0;
 77            if (strName == "btnThread10")
 78            {
 79                intOrder = 10;
 80            }
 81            else
 82            {
 83                intOrder = Convert.ToInt32(strName.Substring(strName.Length - 1));
 84            }
 85            this.thInsert[intOrder - 1] = new Thread(new ParameterizedThreadStart
 86
 87(ThreadSafeInsert));
 88            this.thInsert[intOrder - 1].Start(intOrder);
 89            (sender as Button).Enabled = false;
 90        }
 91
 92        private void frmTestSeqNo_Load(object sender, EventArgs e)
 93        {
 94            for (int i = 1; i <= 11; i++)
 95            {
 96                foreach (Control ctl in this.Controls)
 97                {
 98                    if (ctl.Name == "btnThread" + i.ToString())
 99                    {
100                        (ctl as Button).Click += new EventHandler(btnThread_Click);
101                        break;
102                    }
103                }
104            }
105        }
106
107        private void frmTestSeqNo_FormClosing(object sender, FormClosingEventArgs e)
108        {
109            if (MessageBox.Show(
110                      "R U Sure to Exit?"
111                    , "Threads may still B Alive"
112                    , MessageBoxButtons.YesNo
113                    , MessageBoxIcon.Question
114                    ) == DialogResult.Yes
115                )
116            {
117                for (int i = 0; i < 10; i++)
118                {
119                    if (this.thInsert[i] != null && this.thInsert[i].IsAlive)
120                    {
121                        this.thInsert[i].Abort();
122                    }
123                }
124            }
125        }
126    }
127}

     [下载代码6]

    运行上面的代码，窗体激活时，不断按下Space键触发按钮事件向Invoice表中插入数据。

    程序运行一段时间后，使用代码3检验Invoice表中是否存在InvoiceID <> InvoiceNo的记录。我们会发现，如果初始CurrentNo为0且使用过语句TRUNCATE TABLE Invoice重新创建Invoice表，那么这种方法是可以满足我们的需求的。

    接下来，我们把代码6中调用的存储过程换为以下代码所示的存储过程：

代码7 存储过程GetInvoiceNo_Deadlock
 1CREATE  PROCEDURE dbo.GetInvoiceNo_Deadlock
 2(
 3    @SeqNo BIGINT OUTPUT
 4)
 5AS
 6    BEGIN TRANSACTION
 7    UPDATE dbo.CurrentNo SET CurrentNo = CurrentNo + 1
 8    SET @SeqNo = ( SELECT CurrentNo FROM dbo.CurrentNo )
 9    INSERT INTO dbo.Invoice (InvoiceNo, Type, Status) VALUES (@SeqNo, 'a', 'b')
10    COMMIT
11GO

    运行程序，我们可以发现，一段时间后，出现下图所示的错误：

    图4 死锁

    为什么代码7会出现死锁？由于代码7中SET @SeqNo = ( SELECT CurrentNo FROM dbo.CurrentNo )的本质是一个SELECT语句，因而SQL Server 2000以共享(S)锁锁定资源，而其它线程很可能正好执行到语句UPDATE dbo.CurrentNo SET CurrentNo = CurrentNo + 1，而执行UPDATE语句时SQL Server 2000需要使用排它(X)锁锁定资源，即对于CurrentNo需要从共享(S)锁提升到排它(X)锁，而锁的提升需要时间，线程之间相互等待，因而发生了死锁。而对于代码4来说，一开始即使用排它(X)锁（UPDATE语句），所以不会发生死锁。

    基本上，代码4和代码6的结合可以实现我们的需求，“唯一”肯定没有问题，对于“连续”来说，需要把所有的一次操作包含在事务中。如果你的系统中有多个实体需要使用这种唯一且连续的编号，在CurrentNo中添加一个用于标明实体的字段并简单修改存储过程就可以了。