数据库“锁”事一例：并发情景下重复主键问题方案讨论

在做的一个账单计息功能，其中，账单表的主键是BillId，varchar类型，BillId取值形如B0000001，生成规则是每次新增记录时先从账单表里计算出最大的BillId数字，然后+1再转换后作为新增记录的BillId。例如，B0000001、B0000002。

逻辑很简单，但考虑到并发，技术上就要费点心了。

为了简化场景，这里我写了一些测试用例，涉及到一个包含两个字段的表student（id int, sno varchar(32)）,PK是id。数据库是引擎为InnoDB的Mysql5。InnoDB支持事务操作。

并发处理之lock

这段逻辑用程序来实现的话，用lock关键字，就可以保证多线程情况下同时只能有一个线程来访问资源。

        static object syncRoot = new object();
        private void AddStudent_Lock(string name)
        {
            lock (syncRoot)
            {
                object maxId = ExecuteScalar("select max(id) from student");
                if (maxId == null || maxId == DBNull.Value)
                {
                    maxId = 0;
                }
                int newId = Convert.ToInt32(maxId) + 1;
                string sql = "INSERT INTO student VALUES(" + newId + ",'" + name + "');";
                ExecuteNonQuery(sql);
            }
        }

测试用例：

        [TestMethod]
        public void Test3()
        {
            ExecuteScalar("DROP TABLE IF Exists student;");
            ExecuteScalar("CREATE TABLE student(id INT NOT NULL,sno varchar(255),PRIMARY KEY (id));");
            ExecuteScalar("truncate table student;");

            //Stopwatch watch = new Stopwatch();
            //watch.Start();
            List<Thread> ths = new List<Thread>();
            for (int i = 0; i < 10; i++)
            {
                var thread = new Thread(() =>
                  {
                      try
                      {
                          for (int j = 0; j < 500; j++)
                              AddStudent(Thread.CurrentThread.Name + "--" + j);
                      }
                      catch (Exception ex)
                      {
                          Console.WriteLine(Thread.CurrentThread.Name + "--" + ex.Message);
                      }
                  });
                thread.Name = "thread" + i;
                ths.Add(thread);
            }
            ths.ForEach(t => t.Start());
            Thread.Sleep(15 * 1000);
        }

运行测试，ok。

这个方案解决了多线程下（同一进程内）的并发问题。但，在分布式系统的场景下，项目中的若干应用系统都涉及到生成账单的逻辑，这个方案显然就无能为力了。

数据库锁

看来，如果多个系统都涉及到生成账单的逻辑，其中一个方案是封装这个生成账单的逻辑，然后通过rpc来实现。另一个方案，假定这个逻辑在每个系统里都有，就要在数据库层面来控制了。这里，我要介绍的是后者。

为了避免多个进程同时访问这段逻辑出现重复主键冲突，所以，需要锁表。mysql语句见下：

        public void AddStudent(string name)
        {
            string sql = @"
LOCK TABLES student WRITE;
SELECT @maxid:= MAX(id) FROM student for update;
SET @maxid:=IF(@maxid IS NULL,0,@maxid);
INSERT student VALUES(@maxid+1,@name);
UNLOCK TABLES;";
            ExecuteNonQuery(sql,new MySqlParameter("@name",name));

        }

同样用上面的测试用例来测试，ok。

以上是用mysql实现的。 在SqlServer里，因为t-sql和pl/sql是两大派系，其sql语句是这样子的：

        public void AddStudent(string name)
        {
            string sql = @"
BEGIN Tran;
declare @maxid int;
SELECT @maxid= MAX(id) FROM student with(TABLOCKX);
SET @maxid=case when @maxid IS NULL then 0 else @maxid end;
INSERT student VALUES(@maxid+1,@name);
COMMIT;";
            ExecuteNonQuery(sql, new SqlParameter("@name", name));

        }

为表加了TABLOCKX锁后，其他事务将无法对表做任何读写操作。TABLOCKX与HOLDLOCK是有区别的，如果换成HOLDLOCK，运行测试用例，会出现死锁“事务(进程id xx)与另一个进程被死锁在 锁 资源上,并且已被选作死锁牺牲品。请重新运行该事务。”，见下截图：

一同学说，用存储过程就可以解决这种并发冲突，不过经过测试，这种说法是不对的，即时是在存储过程里，也要加上TABLOCKX和事务。