MySQL SELECT … FOR UPDATE causing table lock during high traffic while generating sequential transaction IDs

Databases
,
1

I have a PHP + MySQL project where I need to generate sequential transaction IDs.
Each transaction can have multiple items, so all rows for that transaction must share the same txn_in value.

Tables

stock_inward

ID | txn_in  | material_code | insert_dt
1  | TXN001  | MT001         | 2025-01-13 14:09:08
2  | TXN001  | MT002         | 2025-01-13 14:09:08
3  | TXN001  | MT003         | 2025-01-13 14:09:08
4  | TXN002  | MT002         | 2025-01-13 15:02:37
5  | TXN003  | MT009         | 2025-01-14 11:01:25
6  | TXN003  | MT006         | 2025-01-14 11:01:25

txn_allot

ID | module   | prefix | session | last_number
1  | STORE_IN | MIN    | 25-26   | 3

Problem

To generate the next transaction number (TXN004, TXN005, etc.) I am using:

SELECT last_number FROM txn_allot 
WHERE module='STORE_IN' 
FOR UPDATE;

Then I increment the number and update last_number.

This works, but when multiple users are entering data at the same time,
the txn_allot table becomes locked, causing big delays until the lock is released.

Question

What is the best way to safely generate sequential, unique transaction IDs without causing table-level locking when multiple concurrent requests occur?

Notes:

  • MySQL database (InnoDB)
  • PHP backend
  • Requirement: transaction IDs must be unique and sequential (no duplicates)
2

why do they have to be sequential? and why do they have to have TXNnnn format?

is there a reason you can’t use auto_increments, which are guaranteed to be unique no matter how many users are entering data?

1 Like
3

Do you have a unique index on the txn_allot table’s module field? I don’t use mysql much these days, but a quick look at the manual suggests that a unique index might be necessary for the row locking to work.

SELECT ... FOR UPDATE and SELECT ... FOR SHARE statements that use a unique index acquire locks for scanned rows, and release the locks for rows that do not qualify for inclusion in the result set

4

Concurrency problems are very hard, I don’t see how you can do it without some kind of locking. And data being king you don’t want to mess that up.

If I were you I move that to the application and use Redis INCR, which increment and return atomically the value (is concurrent safe). You have to make sure Redis is setup to save to disk in case the server goes down, it remember the last number. And is extremely fast.

Of course the problem with this is, it add another layer of complexity. What if Redis goes down how the app should react (always do negative testing go avoid cloudfare type of issue).

5

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.