Transactions, locks and double billing
This article is the 23rd day of CAM Engineer Advent Calendar 2019. Yesterday was @ cotsupa's I made a search command with Shell Script.
Introduction
Hello, this is @ takehzi. I did a task to prevent double billing in my business, so it will be a memorandum.
The background is when the user clicks the payment button twice. The payment process was running twice, so I fixed it. (Processing to control is implemented even on the front side)
What is a transaction?
I had only a fluffy understanding of transactions, so I looked it up first. Simply put, a transaction is a unit of work that treats consecutive DB operations (one or more sql operations) as a group.
DB only executes sql and does not know from where to where is one set of processing. Therefore, by setting a transaction (for DB) from this side, the DB side can recognize that this is one set, and treat them as one process (group).
The result of the transaction is ・ Commit ・ Rollback There are only two.
When all the processing in the transaction is successful, issue a commit to enable changes to all relevant tables and end the transaction.
If any of the operations fail, issue a rollback to invalidate any changes to all relevant tables (in the state they were in before entering transaction processing) and end the transaction.
Lock
As a measure to prevent double billing, it is necessary to perform lock control for the DB, This time, I decided to use "SELECT ... FOR UPDATE" to perform exclusive lock (pessimistic lock).
Also, there is a good chance that another user will make a payment while double billing is taking place. Make sure the row is locked.
Since you need to index (or condition the primary key) to lock the row, You also need to index it in advance.
I will try using SELECT ... FOR UPDATE.
mysql> select * from user;
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| 1 | takehzi | 25 |
| 2 | komatsunana | 23 |
| 3 | yoshiokariho | 26 |
+----+--------------+------+
3 rows in set (0.00 sec)
Use SELECT ... FOR UPDATE on this table to change the age of takehzi from 25 to 20.
A)
==================================
//Transaction start
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
//id=Lock the row when retrieving 1 record.
mysql> select * from user where id=1 for update;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | takehzi | 25 |
+----+---------+------+
1 row in set (0.00 sec)
===================================
B)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id=1 for update;
//...Waiting
B is waiting because A has a row lock. Then, when A records are updated and committed, the lock is released and B's select statement runs.
================================
A)
//Change age from 25 to 20
mysql> update user set age="20" where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
//End of transaction
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
==============================
B)
mysql> select * from user where id=1 for update;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | takehzi | 20 |
+----+---------+------+
1 row in set (9.26 sec)
B's sql is executed at the timing of committing in A. It's the value I got in B, but it's 20 instead of 25.
I used this SELECT ... FOR UPDATE to control double billing.
Double billing problem and countermeasures
- Prerequisites * ・ TrackingId will be issued when payment is completed. -Authorization (method to perform settlement) processing returns trackingId as a result.
Based on these conditions, we decided to set up a session table to control double billing.
It is controlled by whether or not there is a trackingId.
The architecture diagram is as follows.
The actual code.
public void tempInsert(String serviceName, AmazonPayChargeParam param){
assert StringUtils.isNoneBlank(serviceName);
assert StringUtils.isNoneBlank(param.getReferenceId());
assert StringUtils.isNoneBlank(param.getCustCode());
assert StringUtils.isNoneBlank(param.getItemId());
try{
AmazonPaySession session = new AmazonPaySession();
session.setReferenceId(param.getReferenceId());
session.setCustCode(param.getCustCode());
session.setItemId(param.getItemId());
session.setServiceName(serviceName);
amazonPaySessionRepository.insert(session);
}catch (DuplicateKeyException dke){
//Controls first-place constraint errors due to duplicate inserts.
auditLogHelper.loggingParam(dke.getMessage());
}
}
First, insert is processed in the session table as temporary data. (At this point, trackingId is null)
public String authorization(Long apiAuthId, String serviceName, AmazonPayChargeParam param) {
ChargeRequest chargeRequest = getChargeRequest(param);
String trackingId = chargeRequest.getChargeReferenceId();
AuthorizationDetails authDetails = null;
switch(chargeRequest.getType()) {
//...Abbreviation
case BILLING_AGREEMENT_ID:
//Lock the row here.TrackingId can be obtained on the duplicate side.
AmazonPaySession sessionData = amazonPaySessionRepository.tempSelectForUpdate(serviceName, param.getReferenceId(), param.getCustCode(), param.getItemId());
//The overlapping side is tracking_Get id and get an early return.
if(Objects.nonNull(sessionData.getTrackingId())){
return sessionData.getTrackingId();
}
//Billing process
authDetails = execBillingAgreement(serviceName, param, chargeRequest, apiAuthId, authDetails, trackingId, sessionData);
//...Abbreviation
//Finally returns trackingId
return trackingId;
}
Here, determine the presence or absence of trackingId, If there is a trackingId (duplicate side), that trackingId will be returned early.
Since "there is no trackingId = payment has not been made yet", the payment process is performed as it is. Make sure to add the issued trackingId to the session table.
In this way, we used SELECT ... FOR UPDATE to control duplicate billing.
At the end
I wrote it pretty loosely (I didn't have time to put it off too much ...) In fact, there are more things to consider.
-What is the transaction isolation level (how much read phenomenon is allowed) -This time, REPEATABLE-READ allows phantom reads. ・ Is the ACID property guaranteed? (I think that the independence depends on what you make) ・ Is the line locked properly? ・ Is there a deadlock?
There are still other points to consider when looking at the system level and code level, such as I briefly summarized the recognition of this renovation.
To be honest, my understanding is still ambiguous, so I would like to continue my studies! Also, when I tried to write an article like this this time, I realized that I was less aware than I expected, so I would like to make such output more positively in the future!