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

Whenever I need sequential numbers I use an auto-increment column. See this tutorial. If you don't have a column you can use for this, you should probably reconsider your database design.

A bit off-topic, but important: I see that you use a few abbreviation, like txn and dt. You probably have a tendency to do this more often. Ask yourself why you cannot use transaction or data_time? Is that a good reason? Note that you nowadays don't need to use abbreviations anymore to save memory or speed up execution, like we did in the eighties. Of course, some abbreviation are known by anyone, but often your code will be easier to read if you don't abbreviate, and readability is one of the main reasons most of us don't write assembler anymore.

MySQL doesn't support a sequence object, but you can simulate it with another table.

mysql> create table seq ( id bigint unsigned auto_increment primary key ); Query OK, 0 rows affected (0.028 sec) mysql> replace into seq (id) values (default); Query OK, 1 row affected (0.006 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 1 | +------------------+ 1 row in set (0.000 sec) mysql> replace into seq (id) values (default); Query OK, 1 row affected (0.002 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 2 | +------------------+ 

Any time you need to generate a new transaction id, repeat the REPLACE command I show above and get the value with LAST_INSERT_ID(). Then use that value inserting into your stock_inward table.

Using REPLACE has the advantage that that table only needs one row, so it will never grow large.

You show something similar with your txn_allot table, but using AUTO_INCREMENT it would be hard to support multiple modules. In that case, use a separate allot table for each module.

"big delays"? Are you immediately updating the table and releasing the lock? The other options mentioned above, but your approach should only have minor delays.

Why do the values have to be sequential? Usually being unique is sufficient, especially for surrogate keys.

This question should really be under the normal QA as it has objective answers.

SELECT FOR UPDATE locks the entries until the transaction is completed. So when you are querying for them, the parallel requests wanting to do the same will also be locked.

You could use SELECT FOR UPDATE NOWAIT which will break any query immediately and you could requeue it later, but I would not recommend it.

As already suggested just

• use AUTOINCREMENT
• remove any prefixes and suffixes, use just numbers (also incredible fast(est))
• when you want them you could either program them on output/export or create a a query which adds them.