Correct MySQL Transaction Processing

If you study the development of the inventory system, you will consider the operations and related to the inventory information from here, in general, MySQL transaction processing problems may occur. Especially regarding data table locking, how can we ensure data integrity when concurrency occurs. Transaction operations, three to be guaranteed

If you study the development of the inventory system, you will consider the operations and related to the inventory information from here, in general, MySQL transaction processing problems may occur. Especially regarding data table locking, how can we ensure data integrity when concurrency occurs. Transaction operations, three to be guaranteed

If you study the development of the inventory system, you will consider the operations and related to the inventory information from here, in general, MySQL transaction processing problems may occur. Especially regarding data table locking, how can we ensure data integrity when concurrency occurs.

Three principles to ensure transaction operations:

Atomicity: a transaction is an atomic operation unit. Modifications to the data are either performed in full or not all;

Consistent: data must be Consistent at the beginning and end of the transaction;

Isolation: the database system provides a certain Isolation mechanism to ensure that transactions are executed in an "independent" environment not affected by external concurrent operations;

Durable: after a transaction is completed, it modifies the data permanently, even if a system failure occurs.

Therefore, we assume that two objects A and B

Concurrent objects A and B

Initial status data Table query results:

Transaction start sequence A-> B

A: Start the transaction.

No commit at this moment

In this status, B starts the MySQL transaction processing:

Obviously, when A does not perform A Commit action, the action in B's transaction cannot be completed and is always in the transaction waiting stage, provided that the time limit is not exceeded and B's action cannot be submitted.

At this moment, if A performs Commit:

In the action of B

Transition

It indicates that transaction A starts to unlock and transaction B can proceed, but transaction B is not committed at the moment (Commit)

Note: here we perform two operations, that is, querying two objects.

A query:

The ID 1 still exists because the result of B is not submitted, but A can still read the data, but the data is the data before the deletion.

However, query against B:

As you can see, the result of object B is being processed, but it is not submitted for unlocking.

Analysis shows that A reads the result set before B does not submit, but B reads the result set operated by itself. When B completes the submission

Result set of A at this moment

It is found that the current State is the same as the set of B, A = B, which is also within the theoretical value range.

From this, we can find that MySQL locks are actually simple. Of course, it also means that when the database performs the lock operation, it can only be the write operation control. For read data, it is often only allowed to access the data before modification, this part of data is often called "dirty" or dirty data. In reality, we often have this requirement. When A performs A write operation, B is expected not to read data, which is the control of read behavior.

In this way, do not check B During concurrency, but this process is exactly the write operation process of A. Although locking prevents concurrent writing, but for B, the data he sees in this process will be modified. That is, when A completes the write operation, the data read by B will be discarded, in this case, the data read by B should be dirty data or invalid data, unless A's action causes transaction rollback and operation failure for some reason, in this way, when the actual data result is consistent with that seen by B, it is determined that B sees valid data, rather than dirty data or invalid data.