Marco Learning notes 16--mysql advanced transaction and isolation levels

Connection Manager: Accept request to create thread authentication user establish secure connection

Concurrency control:

Mbox:mda

C/s: 100 10 mins:

Multi-version concurrency control: MVCC

Lock:

Read Lock: Shared lock

Write Lock: Exclusive lock

LOCK TABLES Tb_name {read| WRITE};

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: (block)
Row Lock:

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

MyISAM: Transaction not supported

InnoDB:

Isolation:
Isolation level:
READ UNCOMMITTED: Unread uncommitted
Read COMMITTED: Reading commit
Repatable READ: Can be reread
SERIALIZABLE: Serializable

Transactions: CPU, I/O
Rdbms
ACID:
Automicity: Atomicity, the database operations caused by the firm are either completed or not executed;
Consistency: Consistency (in isolated state)
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: Submitted but failed to complete
Aborted: Not submitted
Submitted by:

Multi-Transaction execution: Parallel in a way that does not affect each other
Interactions between transactions:
by Data set

Transactions: Concurrent Execution

1. Improve throughput and resource utilization

2. Reduce waiting time

Transaction scheduling:

Recoverable schedule: cross-execution of any 2 transactions does not cause the state of another transaction to change

No cascade scheduling:

Technical means of concurrency control dependencies:
Lock
Time stamp
Multi-version and Snapshot isolation

Start Tansaction: Start
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