What is the best way to store and add an ordinal number in a table?

Question

MySQL

Good afternoon!
Faced with the problem that with a large number of threads in the database, duplicates for the serial number of the document begin to appear.
It works so that before writing to the table, the last sequence number is incremented:

SELECT ui_id+1 FROM deals WHERE user_id = 1 ORDER BY ui_id DESC LIMIT 1;

and transferred to a new record

INSERT INTO `deals` (`id`, `ui_id`, `user_id`) VALUES (NULL, "5", "1")

What is the best way to avoid such a problem?

Answer 1

And why use ui_id at all if id should already be a unique auto-incrementing ID.

Answer 2

Completely lock the table until the end of the write.

LOCK TABLES `deals` FOR WRITE;
...
UNLOCK TABLES;

No other thread will access the table until it is unlocked.

Answer 3

Something like this:
Create a table to store the sequence (better with the MyISAM engine):

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);

Increment:
Getting result:
mysql> SELECT LAST_INSERT_ID();