How to prevent Race Condition for a Java Microservice running across different containers?

[Trader11]

Anyone have experience on solving this problem? What is the best solution?

Note: This means there are multiple JVM running and therefore the often regurgitated answers like synchronization does not work here.

@davidktw

 

[Azzizz81]

I assume you are referring to concurrent database updates, implementing database transactions will do the job

 

[Trader11]

I assume you are referring to concurrent database updates, implementing database transactions will do the job

I mean multiple services trying to update the database. For example, an e-commerce site have a voucher which you can use two times. If somehow we run the voucher redeem different threads on five containers, then how to prevent user from redeeming it more than two times?

 

[Trader11]

I heard must use database to lock. But there is deadlock risk. So any better way?

 

[Azzizz81]

I heard must use database to lock. But there is deadlock risk. So any better way?

Optimistic concurrency

 

[Trader11]

Optimistic concurrency

Interesting. Thanks buddy

 

[davidktw]

I heard must use database to lock. But there is deadlock risk. So any better way?

Dead lock is not created because of locking. It is the order at which you lock and whether it will create inter resources dependency locking scenario. One simple way to look at locking is
Thread 1 lock on resource A and attempt to lock on resource B. Thread 2 lock on resource B and attempt to lock on resource A. In the end deadlock is created since both threads will not let go then first resource that they have acquired and locked. The textbook way of solving this problem is always have both threads acquired the lock in the same order. Practically you may still have this issue. Hence some approaches is lock with time out and retry. Database will also help to detect deadlock and return error for clients to retry in some cases.

In your scenario you wouldn’t even have a locking issue. A database transaction behaviour would have easily prevented by providing mutual exclusiveness in reading and updating the relevant counter in the database

BEGIN TRANSACTION
SELECT count FROM t WHERE id=? FOR UPDATE
// application determine if the count has reached max value
UPDATE t SET count = count + 1 WHERE id=?
COMMIT

Optimistic concurrency works but your client code must be ready to retry on exception. So it depends on your needs.

 

[Trader11]

Dead lock are not created because of locking. It is the order at which you lock and whether it will create inter resources dependency locking scenario. One simple way to look at locking is
Thread 1 lock on resource A and attempt to lock on resource B. Thread 2 lock on resource B and attempt to lock on resource A. In the end deadlock is created since both threads will not let go then first resource that they have acquired and locked. The textbook way of solving this problem is always have both threads acquired the lock in the same order. Practically you may still have this issue. Hence some approaches is lock with time out and retry. Database will also help to detect deadlock and return error for clients to retry in some cases.

In your scenario you wouldn’t even have a locking issue. A database transaction behaviour would have easily prevented by providing mutual exclusiveness in reading and updating the relevant counter in the database

BEGIN TRANSACTION
SELECT count FROM t WHERE id=? FOR UPDATE
// application determine if the count has reached max value
UPDATE t SET count = count + 1 WHERE id=?
COMMIT

Optimistic concurrency works but your client code must be ready to retry on exception. So it depends on your needs.

Question: Is it redundant to use Java native APIs like synchronisation to prevent race condition if we are using optimistic concurrency at database?

 

[davidktw]

Question: Is it redundant to use Java native APIs like synchronisation to prevent race condition if we are using optimistic concurrency at database?

Concurrency at your application layer is not equivalent to concurrency at database layer. Those are very different synchronisation concepts. Synchronisation at database level is to ensure data consistency, repeatability and so forth. Application concurrency is about consistency at your app level. Of course because of dependency ordering you can use application synchronisation to even avoid a call to the database, but that would be an issue when you are scaling outwards because it is not easy to achieve synchronisation inexpensively across multiple hosts without clustering techniques. That is why database always play a very central role in data consistency by offering transactional behaviour. It is to alleviate the need for the application to achieve data consistency.

Optimistic concurrency is to detect inconsistency in read values from updated values. It is not about your app level synchronisation. In the case of the example above.
App 1 will read the value and versioning of the row counter, (Counter, Version1).
App 2 will read the value and versioning of the row counter, (Counter, Verson1).
*No locking is introduced here

App 1 found the Counter < 2, increment it and attempt to write back the new counter value (Counter + 1, Version2) only if the Version is still Version1. (1 row updated, successful)
App 2 also found the Counter < 2, increment it and attempt to write back the new counter value (Counter + 1, Version2) only if the Version is still Version1. (0 row updated, conflict detected, need to retry again.)
*There is locking here. Updating the same row is a locking behaviour itself. If not, the outcome will be inconsistent. UPDATE is an atomic query.

App 2 will read the value and versioning of the row counter, (Counter + 1, Verson2).
App 2 realised the counter has reach 2, make a decision to deny counter incremental

This is the case of optimistic concurrency, which makes the app layer needing to do even more work. So optimistic concurrency is not necessary better if the retry effort is heavy weight. You might as well do eager pessimistic lock to save the cost on heavy preprocesing. Also not every transactions can be easily rollback and retry, especially when you are also required to update data on other data storage system that doesn't support transaction behaviour. The effort to have optimistic concurrency can be non-trivial.

 

[Trader11]

Concurrency at your application layer is not equivalent to concurrency at database layer. Those are very different synchronisation concepts. Synchronisation at database level is to ensure data consistency, repeatability and so forth. Application concurrency is about consistency at your app level. Of course because of dependency ordering you can use application synchronisation to even avoid a call to the database, but that would be an issue when you are scaling outwards because it is not easy to achieve synchronisation inexpensively across multiple hosts without clustering techniques. That is why database always play a very central role in data consistency by offering transactional behaviour. It is to alleviate the need for the application to achieve data consistency.

Optimistic concurrency is to detect inconsistency in read values from updated values. It is not about your app level synchronisation. In the case of the example above.
App 1 will read the value and versioning of the row counter, (Counter, Version1).
App 2 will read the value and versioning of the row counter, (Counter, Verson1).
*No locking is introduced here

App 1 found the Counter < 2, increment it and attempt to write back the new counter value (Counter + 1, Version2) only if the Version is still Version1. (1 row updated, successful)
App 2 also found the Counter < 2, increment it and attempt to write back the new counter value (Counter + 1, Version2) only if the Version is still Version1. (0 row updated, conflict detected, need to retry again.)
*There is locking here. Updating the same row is a locking behaviour itself. If not, the outcome will be inconsistent. UPDATE is an atomic query.

App 2 will read the value and versioning of the row counter, (Counter + 1, Verson2).
App 2 realised the counter has reach 2, make a decision to deny counter incremental

This is the case of optimistic concurrency, which makes the app layer needing to do even more work. So optimistic concurrency is not necessary better if the retry effort is heavy weight. You might as well do eager pessimistic lock to save the cost on heavy preprocesing. Also not every transactions can be easily rollback and retry, especially when you are also required to update data on other data storage system that doesn't support transaction behaviour. The effort to have optimistic concurrency can be non-trivial.

if I have 5 containers running 10 threads each. Is it better to stop race condition at application level first using java apis as it prevent the query to be made. In addition, I include OCC at database level.

How does people do it in real life?

 

[davidktw]

if I have 5 containers running 10 threads each. Is it better to stop race condition at application level first using java apis as it prevent the query to be made. In addition, I include OCC at database level.

How does people do it in real life?

People? Pessimistic concurrency has been there for ages, that is how data mutual exclusion is done with locks.
5 containers with 10 threads each is nothing. A typical database can easily take hundreds or thousands of such locking. It is not like your application only have this locking to do right? Locking for modern database engines are also row locking instead of entire table, so unless you have only 1 variable to update across the entire system, then it should be a concern.

If you want to synchronised at ur app tier, you will need a DLM (distributed lock manager), that is a lot more expensive than locking performed at the database layer. If you are locking using other solution, it is just the same as locking onto the database.

If you want to lower the contention, then implement a manager singleton in each app to take in requests from your 10 threads per app. Implement requests pool to minimise the amount of concurrent request that can be fired from each app. If your concurrency is 5 instead of 50, the serialisation will end up faster than 50 threads fighting.

Have u ever run the above transaction, select and then update across hundreds of processes? Do a POC and see how efficient the database is performing row level locking. You are underestimating the power of a good RDBMS.

Don’t waste time on such issues unless you can quantify the bottleneck, otherwise you will be wasting time on building an elaborate solution that probably isn’t even your bottleneck to start with.

 

[davidktw]

@Trader11

Let me show you the benchmarking so that you will understand why I think you are barking up the wrong tree. Let me show you how efficient a RDBMS is when it come to dealing with contention.

Here is a stored procedure that does exactly the situation of a transaction that select for update, which is a lock, then update row, commit and release the lock

# cat create_doit_50000.sql
DELIMITER $$
DROP PROCEDURE IF EXISTS DOIT;
CREATE PROCEDURE DOIT()
BEGIN
  DECLARE c INT Default 0;
  WHILE c < 50000 DO
    SET c = c + 1;
    START TRANSACTION;
    select counter FROM t WHERE id = 1 FOR UPDATE;
    UPDATE t SET counter = counter + 1;
    COMMIT;
  END WHILE;
END $$

-- vim: sw=2 ts=2 et

The code execute within the database, so you can imagine how fast it will run without network latency locally on the database.
Using the mysql client, I did 1 parallel thread calling the procedure once. Thus it will run 50000 iterations.

# mysql -u root -D test <create_doit_50000.sql
# time $( <doit1.txt parallel --jobs 1 )

real    0m33.476s
user    0m0.728s
sys    0m0.488s

So without any contention, just one single thread, it will take approximately 33seconds, on my 2-cores virtual machine.

Now lets increase the contention to 50 parallel threads, hence only required to run 1000 iterations each

# mysql -u root -D test <create_doit_1000.sql
# time $( <doit50.txt parallel --jobs 50 )

real    0m33.084s
user    0m1.194s
sys    0m0.920s

What did you notice ? Insignificant changes from the 1 thread scenario even when there are 50 threads contenting on the same row in the same table. Lets up the game further

# mysql -u root -D test <create_doit_500.sql
# time $( <doit100.txt parallel --jobs 100 )

real    0m35.296s
user    0m1.658s
sys    0m1.235s

Lets go up to 200 threads

# mysql -u root -D test <create_doit_250.sql
# time $( <doit200.txt parallel --jobs 200 )


real    0m37.666s
user    0m2.306s
sys    0m2.082s

Finally lets whack all the way to 500 threads!

# mysql -u root -D test <create_doit_100.sql
# time $( <doit500.txt parallel --jobs 500 )


real    0m46.584s
user    0m5.183s
sys    0m5.420s

What is your take away from these benchmarks ? Is the MySQL struggling at all ?
I think the reason why it slow down so much in the parallel 500 is not even because the database cannot take it. It's more like my 2-cores VM is not taking it well because the CPU goes up too much running 500 concurrent mysql clients.

Looking at the same 50000 iterations and of various contention from 1, 50, 100, 200, 500, is there very big differences in performance ?

These benchmarks are extreme conditions because it totally neglected
1) Application latency and randomness and inefficiencies to create contention
2) Network latency and randomness between app servers and database servers to create contention
3) It is an artificial contention where it is all focused onto just 1 row in the table.

So you can imagine how extreme the scenarios are in terms of simulating concurrent contention that requires heavy amount of locking in the database. Yet compared to the 1 thread serialised with no contention, the performance of the other scenarios have very little differences.

As such, it pretty much concluded that seeking of perfect solution at the app layer for locking, seeking of optimistic locking instead of simple pessimistic locking technique at your 50 threads concurrency is basically unnecessary. It is considered over-engineering because in real life scenario, unless you have hundreds of servers all whacking a 2-core VM running in a MacBook Pro i7 core system, then you should consider other means of optimisation. Before you even reach that level, there will be a lot other bottlenecks and your database locking is probably not the most important.

I hope these efforts do show you something useful.

Btw, this is what the table contents are after resetting back to zero and run each round above

mysql> desc t;
+---------+------+------+-----+---------+-------+
| Field   | Type | Null | Key | Default | Extra |
+---------+------+------+-----+---------+-------+
| id      | int  | YES  |     | NULL    |       |
| counter | int  | YES  |     | NULL    |       |
+---------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select * from t;
+------+---------+
| id   | counter |
+------+---------+
|    1 |   50000 |
+------+---------+
1 row in set (0.00 sec)

 

[Trader11]

@Trader11

Let me show you the benchmarking so that you will understand why I think you are barking up the wrong tree. Let me show you how efficient a RDBMS is when it come to dealing with contention.

Here is a stored procedure that does exactly the situation of a transaction that select for update, which is a lock, then update row, commit and release the lock

# cat create_doit_50000.sql
DELIMITER $$
DROP PROCEDURE IF EXISTS DOIT;
CREATE PROCEDURE DOIT()
BEGIN
  DECLARE c INT Default 0;
  WHILE c < 50000 DO
    SET c = c + 1;
    START TRANSACTION;
    select counter FROM t WHERE id = 1 FOR UPDATE;
    UPDATE t SET counter = counter + 1;
    COMMIT;
  END WHILE;
END $$

-- vim: sw=2 ts=2 et

The code execute within the database, so you can imagine how fast it will run without network latency locally on the database.
Using the mysql client, I did 1 parallel thread calling the procedure once. Thus it will run 50000 iterations.

# mysql -u root -D test <create_doit_50000.sql
# time $( <doit1.txt parallel --jobs 1 )

real    0m33.476s
user    0m0.728s
sys    0m0.488s

So without any contention, just one single thread, it will take approximately 33seconds, on my 2-cores virtual machine.

Now lets increase the contention to 50 parallel threads, hence only required to run 1000 iterations each

# mysql -u root -D test <create_doit_1000.sql
# time $( <doit50.txt parallel --jobs 50 )

real    0m33.084s
user    0m1.194s
sys    0m0.920s

What did you notice ? Insignificant changes from the 1 thread scenario even when there are 50 threads contenting on the same row in the same table. Lets up the game further

# mysql -u root -D test <create_doit_500.sql
# time $( <doit100.txt parallel --jobs 100 )

real    0m35.296s
user    0m1.658s
sys    0m1.235s

Lets go up to 200 threads

# mysql -u root -D test <create_doit_250.sql
# time $( <doit200.txt parallel --jobs 200 )


real    0m37.666s
user    0m2.306s
sys    0m2.082s

Finally lets whack all the way to 500 threads!

# mysql -u root -D test <create_doit_100.sql
# time $( <doit500.txt parallel --jobs 500 )


real    0m46.584s
user    0m5.183s
sys    0m5.420s

What is your take away from these benchmarks ? Is the MySQL struggling at all ?
I think the reason why it slow down so much in the parallel 500 is not even because the database cannot take it. It's more like my 2-cores VM is not taking it well because the CPU goes up too much running 500 concurrent mysql clients.

Looking at the same 50000 iterations and of various contention from 1, 50, 100, 200, 500, is there very big differences in performance ?

These benchmarks are extreme conditions because it totally neglected
1) Application latency and randomness and inefficiencies to create contention
2) Network latency and randomness between app servers and database servers to create contention
3) It is an artificial contention where it is all focused onto just 1 row in the table.

So you can imagine how extreme the scenarios are in terms of simulating concurrent contention that requires heavy amount of locking in the database. Yet compared to the 1 thread serialised with no contention, the performance of the other scenarios have very little differences.

As such, it pretty much concluded that seeking of perfect solution at the app layer for locking, seeking of optimistic locking instead of simple pessimistic locking technique at your 50 threads concurrency is basically unnecessary. It is considered over-engineering because in real life scenario, unless you have hundreds of servers all whacking a 2-core VM running in a MacBook Pro i7 core system, then you should consider other means of optimisation. Before you even reach that level, there will be a lot other bottlenecks and your database locking is probably not the most important.

I hope these efforts do show you something useful.

Btw, this is what the table contents are after resetting back to zero and run each round above

mysql> desc t;
+---------+------+------+-----+---------+-------+
| Field   | Type | Null | Key | Default | Extra |
+---------+------+------+-----+---------+-------+
| id      | int  | YES  |     | NULL    |       |
| counter | int  | YES  |     | NULL    |       |
+---------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select * from t;
+------+---------+
| id   | counter |
+------+---------+
|    1 |   50000 |
+------+---------+
1 row in set (0.00 sec)

Nice. But how come the pentester says he can perform race condition on my code if database will prevent this? That's why I am asking......

 

[davidktw]

Nice. But how come the pentester says he can perform race condition on my code if database will prevent this? That's why I am asking......

Are you even doing what my test is doing ?
How am I going to know if your code is having race condition ?
Is it even exactly the same as what I'm doing ?

 

[Trader11]

Are you even doing what my test is doing ?
How am I going to know if your code is having race condition ?
Is it even exactly the same as what I'm doing ?

It is different. More like creating new vouchers type of business process. Pentester showed that he can create 5 times when the application only allow 2 times.

 

[davidktw]

It is different. More like claiming vouchers type of business process. Pentester showed that he can claim 5 times when the application only allow 2 times.

If it is a different SQL from what I did, then how can we know your SQLs are correct ?
Did the checking and updating of the counter happens within ONE transaction like the way I have shown you ?

START TRANSACTION;
SELECT counter FROM t WHERE id = 1 FOR UPDATE;
UPDATE t SET counter = counter + 1;
COMMIT;

Did your select introduce an exclusive row lock using "FOR UPDATE" clause ?

 

[Trader11]

If it is a different SQL from what I did, then how can we know your SQLs are correct ?
Did the checking and updating of the counter happens within ONE transaction like the way I have shown you ?

START TRANSACTION;
SELECT counter FROM t WHERE id = 1 FOR UPDATE;
UPDATE t SET counter = counter + 1;
COMMIT;

Did your select introduce an exclusive row lock using "FOR UPDATE" clause ?

Sorry semantic error. I mean creating new vouchers. I didn't use a counter. My code will query the number of vouchers created by user and check if it is more than two.

I have a query to insert a voucher into voucher table

 

[davidktw]

Sorry semantic error. I mean creating new vouchers. I didn't use a counter. My code will query the number of vouchers created by user and check if it is more than two.

I have a query to insert a voucher into voucher table

Then can't, there is no locking mechanisms here.

SELECT COUNT(*) FROM t WHERE condition;

doesn't lock on anything, even if the selected rows are locked, nothing is stopping another thread
from adding another row into the table even it both connections are running transaction.

Using this will work

SELECT COUNT(*) FROM t WHERE condition FOR UPDATE;

But the problem is it will effectively becomes a table lock since it is counting all the rows to know if the condition fits

eg: SELECT COUNT(*) FROM t WHERE id = ? FOR UPDATE;

One quick fix is simply create a table perhaps called

VOUCHERS_PER_USER(userId, no_of_vouchers)

Then start the transaction as such

START TRANSACTION
SELECT no_of_vouchers from VOUCHERS_PER_USER where userid = ? FOR UPDATE

-- application determine if the no_of_vouchers >= 2
-- if true, rollback the transaction
-- if false, continue

INSERT INTO VOUCHERS values (userid, ..., voucher_details, ... )
UPDATE VOUCHERS_PER_USER SET no_of_vouchers = no_of_vouchers + 1
COMMIT

This way the first SELECT statement will create a pessimistic lock for the same user.

 

[Trader11]

Then can't, there is no locking mechanisms here.

SELECT COUNT(*) FROM t WHERE condition;

doesn't lock on anything, even if the selected rows are locked, nothing is stopping another thread
from adding another row into the table even it both connections are running transaction.

Using this will work

SELECT COUNT(*) FROM t WHERE condition FOR UPDATE;

But the problem is it will effectively becomes a table lock since it is counting all the rows to know if the condition fits

eg: SELECT COUNT(*) FROM t WHERE id = ? FOR UPDATE;

Thanks. One idea I am going to try is create a voucher counter table. Before I insert, I will query and lock the counter row to check if the counter is less than 3. If so, I will increment the counter before doing the insert.

 

[davidktw]

Thanks. One idea I am going to try is create a voucher counter table. Before I insert, I will query and lock the counter row to check if the counter is less than 3. If so, I will increment the counter before doing the insert.

Okay wait. I made a mistake. My earlier table doesn't have keys, that's why it resulted in a full table lock.

You need indexes if you wanna create per user vouchers locking upon this query

SELECT COUNT(*) FROM vouchers WHERE userid = ? FOR UPDATE;

In this way, when one connection does this

START TRANSACTION
SELECT COUNT(*) FROM vouchers WHERE userid = 1 FOR UPDATE;

If a 2nd connection also does

START TRANSACTION
SELECT COUNT(*) FROM vouchers WHERE userid = 1 FOR UPDATE;

this 2nd connection will hang until the first rollback or committed

However if there is a 3rd connection that does this

START TRANSACTION
SELECT COUNT(*) FROM vouchers WHERE userid = 2 FOR UPDATE;

The 3rd connection will not hang because the select statement is selecting on the userid(index), not a full table scan, hence only those rows for userid 2 are selected for update and can proceed regardless of the first 2 connections transaction.

Create this table and open 3 mysql client connections and give it a trial.

It means you don't need a counter table.

If you want to see what is going on, I can set up a zoom session with you to show you.
I just don't want to write so much in this forum... too lengthy to explain all the caveats.
PM me if you are interested.

In any case, this technique will also need to take care of deadlocks. Deadlocks will exists when there is no first record found for a user. If there is at least 1 record found, then it would be an issue.