如何处理并发插入数据库而导致违反数据库中记录规则的情况? [英] How to handle concurrent inserts into DB causing violation of a rule for the records in the database?

问题描述

我们有一个CARD表，其中存储卡号和用户帐号.最多两个用户可以共享同一张卡.此约束未实现为对表的SQL约束，而是通过以下方式施加在代码中.

We have a CARD table that stores card number and user account number. Only a max of two users can share the same card. This constraint is not implemented as an SQL constraint on the table but is imposed in the code in the following way.

当用户尝试添加卡时-

1. 检查卡号是否已存在于数据库中.通过查询表中的卡号来完成.
2. 如果没有记录，则表示它是新卡.添加一条记录.
3. 如果已经有记录- 如果没有.的记录数少于2-在表中为此用户添加一条记录. <同一卡号，该用户帐号> 如果它等于2-达到了限制，则我们无法将此卡添加到该用户.因此，代码将引发错误.

1. check whether the card number already exists in the DB. Done by querying the table with the card number.
2. If no records, it means it is a new card. Add a record.
3. If there are already records - if the no. of records is less than 2 - add a record in the table for this user. <same card number,this user account number> if it is equal to 2 - the limit is reached and we cannot add this card to this user. So, the code will throw an error.

现在是问题:

假设有100个用户试图同时添加同一张卡.此卡之前在DB中不存在.将会发生以下情况. 100个线程中处理了100个请求(每个用户一个请求).每个线程都会看到表中没有卡号->编号.添加此卡的用户总数为0，小于限制数.即2.因此，每个线程将尝试在表中插入一条记录.最终，表中将有100位用户共享同一张卡.

Assume that 100 users are trying to add the same card at the same time. This card did not exist in DB before. The following will happen. 100 requests(one request for each user) are processed in 100 threads. Each thread will see that the card number is not present in the table -> the no. of users who added this card is 0 which is less than the limit.i.e, 2 . So, each thread will try to insert a record into the table. In the end, we will have 100 users in the table sharing the same card.

user 1,card number  - thread 1
user 2,same card number - thread 2
user 3, same card number - thread 3.
......
....
user 100, same card number - thread 100

我们最多只允许2个用户共享卡，但是现在我们的共享卡限制已被打破.如何解决这种情况?

We wanted only a max of 2 users to share the card but our shared card limit is broken now. How to tackle this scenario?

一个明显的答案是在数据库中同步插入记录.但是，这导致以下问题.

One obvious answer is synchronization of inserting the record in the DB. But,it leads to the following problem.

假设100个用户试图添加100个不同的卡号.如果我们同步插入操作，则当一个请求尝试将记录插入表中时，所有其他请求将保持等待状态-> API的响应时间 打得很重.但是，在这种情况下，不需要这样做，因为用户，卡号组合对于每个请求都是不同的.因此，排除了该选项.我们现在剩下什么选择了?

Assume 100 users are trying to add 100 different card numbers. If we synchronize insert operation, when one request tries to insert a record into the table, all other requests will be kept waiting->Response time for the API takes a heavy beating. But, in this case, there is no need to do it because the user,card Number combination is different for every request. So, that option is ruled out. What options are we left with now?

推荐答案

您所描述的问题称为写偏斜".

The problem you are describing is called Write Skew.

原则上，SERIALIZEABLE事务隔离可解决此问题， 但是不在Oracle DB中. Oracle仅提供快照隔离，即使您要求SERIALIZABLE.

In principle, SERIALIZEABLE transaction isolation takes care of this, but not in Oracle DB. Oracle only provide snapshot isolation, even when you ask for SERIALIZABLE.

在Oracle中，几乎唯一的可靠解决方案是使用某种锁定.不必一定要锁定表行，您也可以通过DBMS_LOCK使用咨询锁.

In Oracle, pretty much the only relyable solution is to use some kind of locking. This doesn't necessarily need to be a lock on a table row, you can also use advisory locks via DBMS_LOCK for that.

这篇关于如何处理并发插入数据库而导致违反数据库中记录规则的情况?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！