mysql 余额表设计_余额表的设计

原由

在开发ERP应用中，我们经常需要知道某个实体的当前数量，例如知道商品当前的库存，或者科目的金额，或者某个客户剩余的信用额度，所以这种需求是比较普遍的。

通常会设计两张表，一张是流水账表，有的称明细表，或者日志表，用于记录所有发生的事务记录。一张是余额表，用于记录各个实体当前最新的余额。

流水号

NO.

商品编号

ProductId

增加

Add

减少

Reduce

1

P1

3

2

P1

1

3

P2

5

商品编号

ProductId

余额

Banlance

P1

2

P2

5

如果有一张单据分别出货P1和P2各一件，而另外一个单据也出货P2和P1各一件，只是顺序不同而已。所以可能出现下面的sql执行顺序。

顺序

Order

用户1

User 1

用户2

User 2

状态

Status

1

begin tran

begin tran

OK

2

INSERT INTO [Chronological]

([NO], [ProductId],[Reduce])

VALUES (4, ‘P1’, 1);

INSERT INTO [Chronological]

([NO], [ProductId],[Reduce])

VALUES (5, ‘P2’, 1);

OK

3

Update [Banlance]

Set Banlance = Banlance- 1

Where ProductId = ‘P1’

OK

4

Update [Banlance]

Set Banlance = Banlance- 1

Where ProductId = ‘P2’

OK

5

Update [Banlance]

Set Banlance = Banlance- 1

Where ProductId = ‘P2’

Wait…

6

Update [Banlance]

Set Banlance = Banlance- 1

Where ProductId = ‘P1’

Wait

可以看出，当两个用户需要的资源(余额表)很容易产生死锁，这是本文要关键解决的问题。

另外，实际情况还要更复杂，由于不能保证余额表一定存在对应的产品记录，当Update Banlance表未影响任何行时，需要执行一个Insert 指令，你很容易想到的，另外一个用户也正好执行了此产品的Update，也发现未影响任何行兵执行一个Insert指令，而后执行的insert将出现“数据重复”的异常。

解决方案一：顺序的更新数据

第一种解决方案是在执行Update指令时，对要更新的数据进行排序。对于此案例，我们可以对ProductId进行排序，重新执行上面的SQL.

顺序

Order

用户1

User 1

用户2

User 2

状态

Status

1

begin tran

begin tran

OK

2

INSERT INTO [Chronological]