Multithreading and MySQL?

Question

PHP

What's the deal with multithreading in MYSQL? There is one table where there is an id with autoincrement. From an application that has two threads, each opens a connection to the database and inserts into this table. How will id behave? Or how to approach this problem? It's not an option to create two tables. Synchronization is also not very good. Although it seems the latter is the only solution. Or does the database have its own synchronization?

Answer 1

What's the problem?
The database does not care about the problems of the applications that use it. It will process incoming requests in turn and fulfill them. When performing modification operations, it will lock the table for modification. If there are parallel requests to this resource, it will put them in the queue until blocking operations are completed, if there are parallel requests to a non-blocked resource, it will start its execution without waiting for the results of the previous ones.
You have a table with identity. And five hundred inserts arrived at the same time. They will all line up. And they will be worked out (whether they will be executed or not due to incorrect data). The only but, if one application sent in a row not in a transactiontwo inserts, no one guarantees that their idenitity fields after insertion will differ by one.
And you should not implement logic in the client program, for example:
you inserted a value into a table with an identity key, received it on the client, and, according to the habit of a single-user database, decided to get the number of records in the table as the field idenity value (provided that you do not delete data from it) for further action. Here it may not work, because between the last YOUR Insert operation, someone else can insert data and you will not take them into account in the application logic.
PS
Also remember, if you need to perform several logically related operations in a database in a row, then format them as a transaction- logical indivisible block of operations. In this case, the operations will be performed sequentially in a row, the result will be:
- reflected in the data database, provided that all operations are performed correctly
- completely canceled and the database will restore the state in which it was before the first operation in the transaction, provided that any action in transaction failed.

Answer 2

Do you need synchronization at all? Let them write to themselves in the database as they have to, since there is no reading.

Answer 3

Database logic and application logic are two different layers and two different messes. And don't mix them up.
The application doesn't have to think about the database layer, that's NOT its concern.
The level of identification of a resource in a database is the business of the database.
Basically, in any ORM it will be visible.