Database mysql/mariadb Knowledge Point--Transaction transactions

Case: The bank's database stores the user's account information table, when user A wants user B to transfer, under normal circumstances, the balance of a account is reduced, the balance of the B account is increased, but for some reason (such as sudden power outage), when the balance of a account is reduced, the balance of account B does not increase, This causes the security hidden trouble of the database data. Solution: When the balance of a account is reduced, do not modify the data table immediately, but after confirming that the balance of the B account increases, modify the data table at the same time.

Transaction transactions

? Through the previous cases and solutions, we have come up with a whole new concept: a transaction, a series of successive operations that are going to occur or are happening;

Transaction security is a mechanism to protect the continuous operation (completion) at the same time. The meaning of transactional security is to ensure the integrity of data operations.

Follow the acid principle:

• A:atomicity atomicity; All operations in the entire transaction are either successfully executed or rolled back after all failures
• c:consistency consistency; The database is always transitioning from one consistency state to another consistency state
• I:isolation isolation; An operation made by an office cannot be seen by other transactions until it is committed; isolation has multiple isolation levels for concurrency
• D:durability persistence; Once a transaction commits, its modifications are persisted in the database

Life cycle

Explicit transactions: The beginning of a clear set of transactions

Implicit transactions: The default is implicit transactions, which are submitted directly after each sentence is executed

Autocommit = {off| on} to turn autocommit on or off, it is recommended that you use explicit request and commit transactions instead of using the auto-commit feature

To start a transaction:

START TRANSACTION;

Insert Tags:

ROLLBACK to # #;

To undo the specified label:

ROLLBACK to # #;

Revoke all:

ROLLBACK;

Commit TRANSACTION:

COMMIT;

To delete a label:

RELEASE savepoint;

Example

MariaDB [school]> START TRANSACTION;  #明确指明启动一个事务MariaDB [school]> INSERT Students (Stuid,name,age,gender) VALUES (+, ' Tom ', +, ' M ');  #添加一条记录MariaDB [school]> savepoint sp26;  #插入一个标签MariaDB [school]> INSERT Students (stuid,name,age,gender) VALUES (' Maria ', ' F ');  #再加入一条记录MariaDB [school]> SELECT * from students WHERE Stuid in (26,27); #查看一下, you can see the data you just inserted +-------+-------+-----+--------+---------+-----------+| Stuid | Name | Age | Gender | ClassID |    Teacherid |+-------+-------+-----+--------+---------+-----------+| 26 |  Tom | 22 |    M |      NULL |    NULL | | 27 |  Maria | 12 |    F |      NULL |  NULL |+-------+-------+-----+--------+---------+-----------+mariadb [school]> ROLLBACK to Sp26;  #撤销到sp26标签之前的状态MariaDB [school]> SELECT * from students WHERE Stuid in (26,27); #查看一下, just Maria's message was withdrawn +-------+------+-----+--------+---------+-----------+| Stuid | Name | Age | Gender | ClassID | Teacherid |+-------+------+-----+--------+---------+-----------+| 26 |  Tom | 22 |    M |      NULL |  NULL |+-------+------+-----+--------+---------+-----------+mariadb [school]> COMMIT;  #提交事务MariaDB [school]> SELECT * from students WHERE Stuid in (26,27); #最终的数据 +-------+------+-----+--------+---------+-----------+| Stuid | Name | Age | Gender | ClassID |    Teacherid |+-------+------+-----+--------+---------+-----------+| 26 |  Tom | 22 |    M |      NULL | NULL |+-------+------+-----+--------+---------+-----------+

Isolation level

1. READ UNCOMMITTED other transactions can see uncommitted dirty data, resulting in dirty reads
2. Read COMMITTED After commit other transactions can see the modified data, each read data may be inconsistent, non-repeatable read
3. REPEATABLE Read REPEATABLE read, every time you see the same data, the data is modified to see the latest data, will produce a phantom read (default setting)
4. Setializabile uncommitted read transaction blocking modification transaction, serial execution, poor concurrency

MVCC: Multi-version concurrency control, and transaction-level correlation

Modify TRANSACTION ISOLATION Level: Server variable tx_isolation specified, default is Repeatable-read, can be set at global and session level

tx_isolation

• Description: The transaction isolation level. See also SET TRANSACTION isolation level.
• Commandline:

  --transaction-isolation=name

• Scope: Global, Session
• Dynamic: Yes
• Type: Enumeration
• Default Value:

  REPEATABLE-READ

• Valid Values:

  READ-UNCOMMITTED,  READ-COMMITTED,  REPEATABLE-READ,  SERIALIZABLE

View Tx_isolation

MariaDB [school]> SELECT @ @tx_isolation;   #默认为可重复读级别 +-----------------+| @ @tx_isolation  |+-----------------+| Repeatable-read |+-----------------+mariadb [school]> SET tx_isolation= ' read-uncommitted '; MariaDB [school]> set tx_isolation= ' read-committed '; MariaDB [school]> set tx_isolation= ' Repeatable-read '; MariaDB [school]> set tx_isolation= ' SERIALIZABLE ';

Dead lock

? A deadlock occurs when two or more transactions occupy each other in the same resource and request a lock on the state of the resource that is occupied by the other

When a transaction modifies the 3rd row of the T1 table, the B transaction modifies the 2nd row of the T2 table, when a transaction modifies the 2nd line of the T2 table, the a transaction is blocked, and the B transaction has just modified the 3rd row of the T1 table, then the B transaction is blocked, and a deadlock occurs.

Two transactions at the same time to change each other's modified tables, blocking each other, the system will find a deadlock, will automatically sacrifice a small transaction to unlock the deadlock.

ERROR 1213 (40001): Deadlock found when trying to get lock; Try restarting transaction

View the list of processes

MariaDB [school]> SHOW processlist;

Kill Process:

MariaDB [school]> KILL 5;

Concurrent access Control

The implementation of concurrent access control technology is based on the lock;

The lock is divided into table-level and row-level locks, the MyISAM storage engine does not support row-level locks, and InnoDB supports table-level and row-level locks;

The categories of locks have read and write locks, read locks are also known as shared locks, read locks when other people can read; Write locks are also known as exclusive locks or lock locks, and a write lock blocks other read and write operations;

Locks are also divided into implicit and explicit locks, the implicit lock is managed by the storage engine, the explicit lock is the user to manually add the lock;

Lock strategy: The balancing mechanism sought in lock granularity and data security.

How to use an explicit lock:

LOCK TABLES tbl_name read| WRITE

Add read lock

MariaDB [school]> LOCK TABLES students READ;  #加读锁

Unlock

MariaDB [school]> UNLOCK TABLES;  #解锁

FLUSH TABLES tb_name: Closes the table that is being opened (clears the query cache), usually with a global read lock before the backup

SELECT clause [For UPDATE | Lock in SHARE MODE] Add write or read lock when querying

　　

Database mysql/mariadb Knowledge Point--Transaction transactions