"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 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?"
A single-row SELECT ... FOR UPDATE held across the entire transaction causes contention when many users insert items for the same transaction. Generate the next transaction number in a very short, separate atomic operation to avoid long-held locks: options include a dedicated auto-increment sequence table (INSERT to get LAST_INSERT_ID()), MySQL SEQUENCE objects (NEXT VALUE FOR), or an atomic UPDATE ... RETURNING if available. After allocating the numeric id and committing, use that id (formatted with prefix/padding) for all item rows. If absolutely no gaps are permitted, serialize allocation and accept contention; otherwise allow gaps for better concurrency.
Read at SitePoint Forums | Web Development & Design Community
Unable to calculate read time
Collection
[
|
...
]