PHP uses database concurrency issues (optimistic and pessimistic locks)

In the interaction between PHP and the database, if the concurrency is large, and all go to the database modification, there is a problem to be aware. The lock problem of the data. It's going to involve the database transaction and quarantine mechanism.

Database transactions guarantee the acid nature of transactions according to different levels of transaction isolation, which means that transactions are not open to solve all concurrency problems. Typically, there are four possible problems with concurrent operations:

• Update lost: One transaction update overwrites the update of another transaction, and here is the problem of missing the update.
• Dirty reads: One transaction reads uncommitted data from another transaction.
• Non-REPEATABLE READ: One transaction reads the same data two times and two reads inconsistent data.
• Phantom reads: One transaction reads a range of records two times, and the number of records read two times is inconsistent.

The database typically has four different levels of transaction isolation:

Isolation level	Dirty Read	Non-REPEATABLE READ	Phantom reading
Read UNCOMMITTED	√	√	√
Read committed	X	√	√
REPEATABLE READ	X	X	√
Serializable	X	X	X

The default transaction isolation level for most databases is read Committed, while the transaction isolation level for MySQL is read repeatedly (Repeatable read). For missing updates, only the serialization (Serializable) level can be completely resolved. For high-performance systems, however, transaction isolation using serialization levels can lead to a dramatic drop in deadlock or performance. Therefore, it is necessary to use pessimistic lock and optimistic lock. In concurrent systems, pessimistic locks (pessimistic Locking) and optimistic locks (optimistic Locking) are two common locks:

• Pessimistic locks believe that the probability that someone else is accessing the data being changed is very high, so lock the data when the data starts to change until the change is complete. Pessimistic locks are typically implemented by a database (using SELECT ... For UPDATE statement).
• Optimistic locking believes that the probability that someone else is accessing the data being changed is very low, so the data will not be locked until the modification is ready to commit the changes to the database, and released after the change is complete.

***
Take MySQL as an example:
MyISAM storage engine uses a table shrink
InnoDB using row locks (which are explicitly specified as primary keys, otherwise table locks) and table locks

The general practice is to:
1 Opening a transaction
2 Making data changes
3 rollback or commit

In the specific business logic, due to the different isolation mechanisms, resulting in different results.
Optimistic locks and pessimistic locks are used more.

Because pessimistic locks start to lock at the start of a read, performance can become worse in the case of large concurrent accesses. For MySQL Inodb, finding data by specifying an explicit primary key is a single row lock, while a query-scoped or non-primary key operation locks the table.

PHP uses database concurrency issues (optimistic and pessimistic locks)