Location>code7788 >text

Database Transaction Control

Popularity：70 ℃/2024-11-07 12:34:30

The Four Characteristics of a Transaction (ACID)

1.1 Atomicity

Atomicity says that operations in a transaction either succeed or fail together, transaction commit and transaction rollback.

1.2 Consistency

Consistency mainly describes the state of the data in the database before and after the transaction to ensure consistency.

The transaction is submitted successfully, so the balance on Zhang San's account is $900 and the balance on Li Si's account is $100.

The transaction submission fails, then the amounts in the accounts of Zhangsan and Lisan remain unchanged.

This indicates that data consistency is now ensured under the control of the database's transactions.

1.3 Isolation

A manifestation of isolation, where multiple concurrent transactions are isolated from each other.

1.4 Durability

Persistence is reflected in the fact that once the data is committed, the changes to the data are permanent.

Concept note

The following concepts are what transaction isolation levels actually address, so you need to figure out what they all mean.

dirty reading

Dirty read refers to read the other transaction did not commit the data, did not commit means that these data may be rolled back, may not end up in the database, that is, not necessarily the existence of the data. Reading data that does not necessarily end up existing is a dirty read.

repeatable

Repeatable read means that within a transaction, the data read at the very beginning and the same data read at any point before the end of the transaction are the same. Usually for data **UPDATE** operations.

Transaction 1: Executed by the following SQL statement:

START TRANSACTION.
SELECT balance FROM bank_account WHERE account_number = 'A123456';
-- In the meantime, the other transaction performs an UPDATE operation to change the balance to 2000.00.
SELECT balance FROM bank_account WHERE account_number = 'A123456';
COMMIT.

Transaction 2: Executed by the following SQL statement:

UPDATE bank_account SET balance = 2000.00 WHERE account_number = 'A123456';

existTransaction 1in the SELECT, even if it occurs between two SELECTs.Transaction 2The UPDATE operation, because transaction 1 is under repeatable read isolation level, returns the same value (1000.00) for both SELECTs, i.e., the value read by the first SELECT is the same as the value read by the second SELECT, due to the fact that transaction 1 always maintains a snapshot of the data that has been previously read.

non-repeatable

In contrast to repeatable reads, non-repeatable reads refer to the fact that the same batch of data read at different moments within the same transaction may not be the same, and may be affected by other transactions, for example, other transactions changed the batch of data and committed it. Usually for data **UPDATE** operation.

fantasy reading

Phantom read is for data ** insertion (INSERT) ** operation. Assuming that transaction A on the content of certain rows of changes, but has not been submitted, at this time, transaction B inserted with the transaction A before the change of the record of the same record rows, and in the transaction A submitted before the submission of the first, and at this time, in the transaction A in the query, it will be found that it seems as if just change for some data did not play a role, but in fact, the transaction is just inserted into the B, so that the user feels very magical, feel the hallucination, which is called phantom reading.

Now, there are two concurrent transactions:

Transaction A: Perform the following operations:

START TRANSACTION;
SELECT * FROM orders WHERE status = 'pending';
-- in the meantime，(political, economic etc) affairsBImplemented INSERT INTO orders (id, order_number, status) VALUES (101, '123456', 'pending');
SELECT * FROM orders WHERE status = 'pending';
COMMIT;

Transaction B: The following operation is performed between two SELECTs in transaction A:

INSERT INTO orders (id, order_number, status) VALUES (101, '123456', 'pending');

In this example, transaction A has inserted a new order data by transaction B between SELECTs. During the second SELECT operation in transaction A, the new order data inserted by transaction B is discovered, even though it was not seen in the first SELECT. This phenomenon is known as phantom reading, because it is like hallucinating when you see data that seems to have just been added in the same transaction.

Irrepeatable reads are mainly related to update operations on the same row of data, while phantom reads are mainly related to data insertion operations. In practice, non-repeatable reads and phantom reads may occur at the same time, depending on the specific operation of concurrent transactions and isolation level settings.

Transaction isolation level

Transaction isolation is actually to solve the above mentioned dirty reads, non-repeatable reads, phantom reads and these problems, the SQL standard defines four isolation levels, which are all supported by MySQL. The four isolation levels are:

  1. Read Uncommitted (READ UNCOMMITTED)
  2. READ COMMITTED
  3. REPEATABLE READ
  4. SERIALIZABLE

From top to bottom, the isolation strength gradually increases and the performance gradually deteriorates. Which isolation level to use depends on the tradeoffs of system requirements, with **"repeatable reads "** being the default MySQL level.

After 5.7.20, you can query the isolation level of a MySQL database with the following two commands

SELECT @@tx_isolation;
show variables like 'tx_isolation';

The following shows how well the four isolation levels address these three issues

-Isolation level dirty reading non-repeatable fantasy reading
Read Uncommitted (READ UNCOMMITTED) likelihood likelihood likelihood
READ COMMITTED unlikely likelihood likelihood
REPEATABLE READ unlikely unlikely likelihood
SERIALIZABLE unlikely unlikely unlikely

typical example

-- 1、repeatable （No Explicit Locks）
BEGIN;

SELECT pay INTO @pay_value FROM pay_info where id=1;

SET @pay_value = @pay_value + 100;

UPDATE pay_info SET pay = @pay_value where id=1 ;

COMMIT;


-- 2、repeatable+Optimist Lock
BEGIN;

SELECT pay INTO @pay_value_old FROM pay_info where id=1;

SET @pay_value = @pay_value_old + 100;

UPDATE pay_info SET pay = @pay_value where pay = @pay_value_old;

COMMIT;


-- 3、repeatable+line lock
BEGIN;

SELECT pay INTO @pay_value_old FROM pay_info WHERE id=1 FOR UPDATE;

SET @pay_value = @pay_value_old + 100;

UPDATE pay_info SET pay = @pay_value ;

COMMIT;

1. Scope and timing of the lock

  • No Explicit Locks: In the first example, while theSELECT The operation does not explicitly request a lock, but the database may implicitly add a lock depending on the isolation level of the transaction and the query conditions. However, the scope and timing of such implicit locks may not be as clear and controllable as explicit locks.
  • explicit plus line lock: UseSELECT ... FOR UPDATE will explicitly tell the database system that you want to put a row lock on this record until the end of the transaction (commit or rollback). This ensures that no other transaction can modify this row while the transaction is executing.

2. Concurrency control

  • No Explicit Locks: Since locks are not explicitly requested, concurrency control relies more on the default behavior and isolation level of the database. In some cases, this can lead to unpredictable behavior, especially when multiple transactions attempt to modify the same data at the same time.
  • explicit plus line lock: Explicit locking provides developers with more concurrency control. It allows the developer to specify exactly what data needs to be locked during a transaction, thus reducing the possibility of data contention and conflict.

3. Performance and resource utilization

  • No Explicit Locks: In some cases, implicit locks may be more efficient than explicit locks because they are automatically managed by the database system and can be optimized as needed. However, this also depends on the specific database implementation and query pattern.
  • explicit plus line lock: Explicit lockingmay add some performance overhead because the database system needs to maintain lock status and handle thecompetition between locks. However, this overhead is usually acceptable, especially when there is a need to ensure data consistency and integrity.




Follow WeChat for more technical articles.

image

<Previous Next>