数据库“锁”事一例:并发情景下重复主键问题方案讨论
在做的一个账单计息功能,其中,账单表的主键是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和事务。
当看到一些不好的代码时,会发现我还算优秀;当看到优秀的代码时,也才意识到持续学习的重要!--buguge
本文来自博客园,转载请注明原文链接:https://www.cnblogs.com/buguge/p/5977700.html