Linux Operations Phase IV (IV) MySQL lock, transaction

Linux Operational Phase IV (IV) MySQL Lock, transaction

1. Related concepts:

Connection Manager: Accept requests, establish secure connections, authenticate users, etc.

Thread Manager: thread pool, threading reuse thread-reuse;

Parser: Parse tree;

Caching: Complexity, algorithms, permissions, etc.;

>select Current_time (); (This command does not cache)

concurrency control problem: More than two users simultaneously read and write the same file data, multi-version concurrency control MVCC, time snapshot;

Lock (The simplest concurrency control mechanism): Read lock (Shared lock), write lock (exclusive lock, exclusive lock);

>help LOCK

>lock TABLES Tb_name [read| WRITE];

>UNLOCK;

Lock Granularity Division: from large to small, table lock (lock whole table), page lock (data block, one piece has multiple lines), row lock,MySQL only supports table lock, row lock needs to be completed by the storage engine, the more coarse the lock is easier to manage, the finer the lock, the more complex the interior.

Note: If there are multiple users writing a table at the same time, the lock operation will be done automatically inside MySQL, we do not need to lock, in the implementation of the database warm backup only need to lock operation.

2. Transaction Transaction

ACID (atomicity; consistency;isolation;durability):

Atomicity atomicity: The DB operations caused by the firm are either completed or not executed;

Consistency consistency: After the execution of the transaction, the entire server state has not changed, the sum before the transaction is consistent with the sum after the transaction (if executed in isolation), such as: Bank account A (3000)--"B (2000);

Isolation Isolation: Transaction scheduling (minimal impact between transactions),MVCC(Multi-version concurrency control);

Durability Persistence: Once a transaction is successfully completed, the system must ensure that any failures do not cause the transaction to exhibit inconsistencies.

There are four levels of isolation, from low to High:

READ UNCOMMITTED not submitted;

Read COMMITTED reads the submission;

Repeatable READ can be reread;(MySQL default )

The SERIALIZABLE can be serialized.

>show GLOBAL VARIABLES like ' tx_isolation ';

>select @ @tx_isolation; (two ways to view isolation levels)

>set global|  SESSION tx_isolation= ' read-uncommitted '; (Modify isolation Level)

Note: The lower the isolation level, the greater the interference between transactions, but the higher the level of isolation, the weaker the concurrency, but the better the security. The isolation level is lowered to improve performance.

Additions and deletions to change the first in memory, and then write to the transaction log, the time is synchronized to the data file (disk space), so that on the transaction engine each write operation will be performed two times, one from memory to the transaction log (fast, log only the operation process), once the transaction log is written to the data file (persistent).

Memory-"Transaction log (undo); Transaction log-" disk (synchronous)

Guaranteed ACID Compatibility:redo log(redo logs),undo Log(undo logs).

log groups: Log files are critical, not as large as possible, depending on the transaction requirements. When you start MySQL, the transaction log is synchronized to disk and cannot be terminated, or MySQL may crash.

The transaction log is taken out as far as possible to put another disk.

>start TRANSACTION; (Start transaction)

> multiple SQL statement execution, example:>insert into Tb_name ...; (in a transaction SQL statements are whole, either executed, or not executed)

>COMMIT;  or >ROLLBACK; (The transaction cannot be withdrawn once it is submitted)

>select @ @autocommit; (if not explicitly start the transaction,autocommit can achieve automatic submission, each operation is submitted directly, it is recommended to explicitly use transaction >start TRANSACTION; and turn off auto-commit, system performance is high, less IO operation, better)

>set GLOBAL autocommit=0; (Auto-commit is turned off and can be written to the configuration file permanently)

>help savepoint (Preservation point,>savepoint sp_name; )

>start TRANSACTION;

>delete from Tutors WHERE age<25;

>savepoint AB; (no numbers can be used to save names)

>insert into Tutors (tname,gender,age) VALUES (' Jowin ', ' M ', 25);

>savepoint AC;

>rollback to AB; (roll back to previous save point)

>COMMIT;

Multiple transactions are executed concurrently (in a way that does not affect each other in a manner that increases throughput and resource utilization and reduces wait time), and the interaction between transactions passes through the dataset.

Concurrency control relies on the technical means: lock (Starve-lock hunger, to lock but not to apply; deadlock), timestamp, multi-versioning, and snapshot isolation.

The state of a transaction: an activity, part of a submission, a submission, a failure, or an abort.

Transaction scheduling: Can be resumed scheduling, no cascade scheduling.

To view the transaction isolation level's impact on transactions: on two terminals

>select @ @tx_isolation;

>set tx_isolation= ' read-uncommitted '; (Switch the isolation level back and forth on one terminal)

>update tb_name SET ...;

>ROLLBACK;

>COMMIT;

>select * from Tb_name; (view at another terminal)

This article is to learn "ma Brother Network Video" made notes.

This article is from the "Linux operation and maintenance of difficult learning notes" blog, declined reprint!

Linux Operations Phase IV (IV) MySQL lock, transaction