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)