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

Developer Account — Thu, 27 Nov 2025 15:11:40 +0000

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)

DEV Community: Developer Account

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