MySQL transaction and isolation levels

MySQL transaction and isolation levels

Connection Manager:

Accept Request

Creating Threads

Certified Users

Establish a secure connection

Concurrency control:

The simplest mechanism is to use locks

Multi-version concurrency control: MVCC (using other mechanisms)

Lock:

Read Lock: Shared lock

Write Lock: Exclusive lock

Locking:LOCK TABLES tb_name {read| WRITE};

Unlock:UNLOCK TABLES

Lock granularity: From large to small, MySQL server only supports table-level locks, row locks need to be completed by the storage engine;

Table Lock:

Page Lock:

Row Lock:

Transaction:

Rdbms:acid (atomicity, consistency, isolation, persistence)

MyISAM: Transaction not supported

InnoDB: Support Transactions

Transaction log: Provides acid compatibility for transactions (logs are just a record of the operation process)

Redo Log

Redo Log

Undo Log

Undo Log

Isolation:

Isolation level:

READ UNCOMMITTED: Unread uncommitted

Read COMMITTED: Reading commit

Repatable READ: Can be reread

Seriablizable: Serializable

To query the default isolation level:

Show global variables like '%iso% ';

Multi-transaction simultaneous execution: parallel to each other in a non-interfering manner;

Interactions between transactions:

by Data set

Transactions: CPU, I/O

Rdbms

ACID:

Automicity: Atomicity, the database operations caused by the firm are either completed or not executed;

Consistency: Consistency, A (-->B) (2000)

1:A:3000-->2500,

2:A+B:4500,

Isolation: Isolation

Transaction scheduling: Minimal impact between transactions

MVCC: Multi-version concurrency control

Durability: Once a transaction is successfully completed, the system must ensure that any failures do not cause inconsistencies in the transaction;

1, the transaction before the submission of data has been written to the persistent storage;

2, combined with transaction log completion;

Transaction log: Sequential IO

Data file: Random IO

Status of the transaction:

Active: Active

Partially committed: After the last statement is executed

Failed:

Discontinued:

Submitted by:

Transactions cannot be revoked once they are submitted

Transactions: Concurrent Execution

1. Improve throughput and resource utilization

2. Reduce waiting time

Transaction scheduling:

recoverable scheduling;

No cascade height:

Technical means of concurrency control dependencies:

Lock

Time stamp

Multi-version and Snapshot isolation

START TRANSACTION | Begin: Start a transaction

Sql

Sql

Commit: Submit

ROLLBACK: Rolling back

If the transaction is not explicitly started:

Autocommit: Can achieve automatic submission, each operation is submitted directly;

Recommendation: Explicitly use transactions, and turn off autocommit;

Save point: SavePoint SID

Rollback to save point: ROLLBACK to Sid

This article is from the "bustling Down" blog, please be sure to keep this source http://chenxujiang.blog.51cto.com/11737025/1846422

MySQL transaction and isolation levels