The transaction

Q&A

Dirty write

Now there are two transactions A and B, A and B are modifying A data at the same time, A updates first, B updates again, but B rolls back, so A is not written in, the data is still the original value, this situation is called dirty write

Dirty read

There are two transactions A and B. A reads the data of B, which has been modified, but has not committed the data of the transaction. Then the transaction B rolls back, causing the data read by A to be different from the data stored in the database

Unrepeatable read

That is, each time the data is read, it is different from the last data read

A transaction has started A transaction, for example, read into A data value, and at the same time A transaction B to modify the data and transaction commit, the transaction again A query, is found just data values change, become data B, if at this time, the transaction has not yet been submitted, and came to A transaction C changes and submit the data, Then transaction A queries the data again and it becomes data C. This phenomenon that the same value is queried multiple times in A transaction, but the query result changes each time is called non-repeatable read.

The non-repeatable default avoids dirty reads because the transaction currently being queried will not see the new value until another transaction has committed

Phantom read

When a transaction runs multiple queries using the same SQL, each time it sees more data than the last time, this phenomenon is called a phantom read

Long transaction

Try not to use long transactions

Long transactions mean that there are old transaction views in the system. Since these transactions can access any data in the database at any time, any rollback records that may be used in the database must be retained until the transaction commits, which can lead to a large storage footprint.

In MySQL 5.5 and earlier, rollback logs are stored in ibData files along with the data dictionary, and the file does not get smaller even if long transactions are eventually committed and rollback segments are cleaned. I’ve seen only 20GB of data and 200GB of libraries for rollback segments. Eventually the entire library had to be rebuilt to clean up the rollback section.

Query, Transaction

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
Copy the code

level

Have not been submitted

read uncommitted

At this level of transaction, dirty writes are not allowed, that is, it is not possible for two data to update the value of the same row without committing, but at this level of isolation, dirty reads, non-repeatable reads, and phantom reads can occur

Read Commit (RC)

read commited

If a transaction is not committed, no other transaction can read the modified value of the transaction.

Repeatable (RR)

Default transaction level for mysql

This level, not write dirty, dirty read and not repeatable problems, many times because you are a transaction query a data value, then the other transaction to modify this value and submit, but you still can’t read other modified the value of the transaction, your transaction once you begin, multiple queries a value, will continue to read the same value

serialization

This level means that no transactions will be executed concurrently, one by one

This level is not typically chosen for production because of poor database performance

MVCC

Undolog version chain

Update this value, add a row to the undolog version chain, update txr_id to the library ID, and roll_pointer to the previous value

ReadView

When a transaction is executed, a ReadView is generated

Four parameters

  • M_ids means that there are transactions running in mysql that have not yet been committed
  • Min_trx_id is the smallest value in m_IDS
  • Max_trx_id is the next transaction di to be generated by myslq, which is the largest transaction ID
  • Creator_trx_id is the transaction ID of the current transaction

Implementation process

There is a row of data in the database

SQL > select * from user where id=45 and id=59; SQL > select * from user where id=59

  1. A transaction generates A ReadView,

    • 45,59 m_ids
    • min_id 45
    • max_id 59
    • creator_trx_id 45

  2. If the txr_id of the current row is less than min_trx_id in the ReadView, it means that the transaction has been completed before the transaction starts, so you can see the result of this transaction, namely the number of rows

  3. B updates this data, so B’s transaction ID is txr_id, B’s value is put there, and roll_pointer points to the original value

  4. Trx_id = min_trx_id = min_trx_id = min_trx_id = min_trx_id = 32 You can read this row because you know it was executed before your transaction

  5. The final read is the original value

RC

The RC implementation is based on the undolog version chain and the ReadView. In this way, a ReadView is generated for each query. This ensures that, even if both transactions are started at the same time, one of the writes succeeds first and the other reads the modified value

The point is: M_ids indicates that the transaction id is between min_trx_id and max_trx_id, but is not in m_IDS. This means that both transactions started at the same time, but one of them committed first, so the other transaction can see the value of the committed transaction

RR

Rr solves the problem of unrepeatable and phantom reads with undolog version chains and ReadView

The problem of unrepeatable and illusory reads is solved by generating readViews only once

Let’s use the example above to illustrate

Transaction A is the query, transaction B is the update data, and transaction A generates A ReadView when transaction A first queries

  • 45,59 m_ids
  • min_id 45
  • max_id 59
  • creator_trx_id 45

Transaction A’s first query, before transaction B commits, reads the original value because 32 is less than min_id

59 In min_IDS, also between min_id and max_id, indicating that transaction A and transaction B are started at the same time, so even if transaction B commits, TRANSACTION A still cannot read the value changed by transaction B, and it still reads the original value

The transaction id in min_IDS is between min_id and max_id. This means that transaction A and transaction B are started concurrently, and only previously committed data can be read whether or not your transaction is committed

If the value of A transaction is greater than the value of B’s transaction ID, the value of B’s transaction id must be greater than the value of B’s transaction ID. If the value of B’s transaction id is greater than the value of MAX_id, the value of B’s transaction id must be greater than the value of B’s transaction ID. So you go ahead, you go ahead and you get unrepeatable, and then you get raw data

Start the way

Two kinds of

  • Explicitly start a transaction statement, begin or Start transaction. The corresponding commit statements are COMMIT and rollback statements are rollback.
  • Set autocommit=0, this command will turn off autocommit for this thread. This means that if you only execute a SELECT statement, the transaction is started and will not commit automatically. The transaction persists until you actively execute a COMMIT or ROLLBACK statement, or disconnect.

Begin /start transaction commands are not the starting point of a transaction, the transaction is actually started by the first statement that operates on the InnoDB table after they are executed. If you want to start a transaction immediately, use the start Transaction with consistent snapshot command.

Transaction propagation

Transaction Propagation – Propagation

REQUIRED

Use the current transaction, if there is no transaction, then create a new transaction, submethods must run in a transaction;

If a transaction exists, join it as a whole.

Example: leaders have no rice to eat, I have money, I will buy their own to eat; When leaders have something to eat, they will share it with you.

SUPPORTS

If there is a transaction, use it; If there is no transaction, then no transaction is used.

Example: leaders have no food, I also have no food; The leader has food to eat, SO do I.

MANDATORY

This propagation property enforces the existence of a transaction and throws an exception if it does not exist

Example: the leader must be in charge of the meal, no matter whether the meal has no meal to eat, I am not willing to quit (throw exception)

REQUIRES_NEW

If there is a transaction, suspend it and create a new transaction for your own use.

If there are no transactions currently, the same as REQUIRED

Example: leaders have meals to eat, I don’t want, I bought their own to eat

NOT_SUPPORTED

If there is a transaction, suspend the transaction and do not apply the transaction to run the database operation yourself

Example: the leadership has rice to eat, share a little to you, I am too busy, put aside, I do not eat

NEVER

Throws an exception if a transaction currently exists

Example: The leader has a meal for you to eat, I do not want to eat, I love work, I throw exceptions

NESTED

If there is a transaction, the subtransaction (nested transaction) is enabled. Nested transactions are committed independently or rolled back.

If there are no transactions currently, the same as REQUIRED.

But if the primary transaction commits, it will commit along with its subtransactions.

If the primary transaction rolls back, the sub-transactions roll back together. In contrast, if the child transaction is abnormal, the parent transaction may or may not be rolled back.

Example: the leader makes wrong decisions, the boss blames him, and the leader suffers with his younger brother. When my younger brother makes a mistake, the leader can pass the buck.