Mysql Transactions written in the same table

Question

On seeing this answer I was left with this doubt:

If we have 2 transactions to be treated at the same time, in the first (A) we are writing in table x, in the second (B) if we try to write in table x at the same time, what happens?

Where "write" can be updating, insertion or removal:

• (A) removes registration y, (B) updates registration y;
• (A) enters new registration, (B) inserts new registration;

Answer 1

Depending on the case the second transaction will be blocked until the first one is over.

Some Engines mysql as the Innodb withstand lock per record (Row-level Locking), while in others the crash is per page or by the whole table.

Then, in a well-configured bank, two transactions can write to the same table without blocking the execution, as long as they are not changing the same record.

Specifically about the quoted answer, it deals a lot with the levels of isolation, that is, as a transaction B see data that is changed by a parallel transaction A. Depending on the setting, the transaction B can see the data not yet effective (committed) by the transaction A. In another configuration, the transaction B sees the original data as if A did not exist.

Anyway, I won’t go into the answer again, but that’s the idea.

On the question of SIMULTANEOUS INSERTS, if the table uses auto increment, as documented, this generates a unique lock until the end of the transaction. However, when using keys generated by some other source will not have competition problems.

If the engine is Innodb and the isolation level is READ LOCAL, as documented, it is possible to make simultaneous Inserts.

For Myisam tables there is a parameter called concurrent_inserts to define how many transactions will be allowed to enter at the same time.

In the case of removal (DELETE) or update (UPDATE), in general new transactions that attempt to execute the actions will be blocked when there is a transaction in progress possessing the "right" over the records in question (lock).