1000 Line MySQL Learning notes (vii)

/* Transaction (TRANSACTION) */------------

A transaction is a logical set of operations that make up the units of this set of operations, either completely or unsuccessfully.

- supports collective success or collective revocation of continuous SQL.

- Transaction is a function of the database in the data late self-study.

- You need to use the InnoDB or BDB storage engine to support the auto-commit feature.

- InnoDB is known as a transaction-safe engine.

--Transaction Open

START TRANSACTION; or BEGIN;

When a transaction is turned on, all SQL statements executed are considered SQL statements within the current transaction.

--Transaction Submission

COMMIT;

--Transaction rollback

ROLLBACK;

If There is a problem with some operations, map to before the transaction is opened.

--Characteristics of the transaction

1. atomicity (atomicity)

A transaction is an inseparable unit of work that either occurs in a transaction or does not occur.

2. Consistency (consistency)

The integrity of the data before and after the transaction must be consistent.

-When the transaction starts and ends, the external data is consistent

-During the entire transaction, the operation is continuous

3. Isolation (isolation)

When multiple users access the database concurrently, the transaction of one user cannot be disturbed by the things of other users, and the data between multiple concurrent transactions is isolated from each other.

4. Persistence (Durability)

Once a transaction is committed, it changes the data in the database to be permanent.

--Implementation of the transaction

1. Required is the type of table supported by the transaction

2. Open a transaction before performing a set of related operations

3. When the entire set of operations is completed, the commit is successful, and if there is a failure, the rollback is returned to the backup point where the transaction begins.

--the principle of business

Use InnoDB's Auto-commit (autocommit) feature is complete.

After the normal MySQL execution statement, the current data commit operation can be visible to other clients.

The transaction is a temporary shutdown of the autocommit mechanism, which requires a commit to commit persisted data operations.

-Note

1. Data definition Language (DDL) statements cannot be rolled back, such as statements that create or cancel a database, and statements that create, cancel, or alter a table or stored subroutine.

2. Transactions cannot be nested

--Save Point

SavePoint Save Point Name --Set a transaction savepoint

ROLLBACK to SavePoint Save Point Name --rollback to SavePoint

RELEASE savepoint Save Point Name --delete savepoint

--InnoDB Auto-commit feature setting

SET autocommit = 0| 1; 0 means turn off autocommit and 1 to turn on autocommit.

- If it is turned off, the result of the normal operation is not visible to other clients, and a commit commit is required to persist the data operation.

- You can also turn off auto-commit to turn on transactions. But unlike the start transaction,

Set AUTOCOMMIT is the setting that permanently alters the server until the next time it is modified again. (For current connection)

and Start transaction records the state before opening, and once the transaction commits or rolls back, the transaction needs to be opened again. (For current transaction)

/* Lock Table */

Table locking is only used to prevent other clients from improperly reading and writing MyISAM support table locks, InnoDB supports row locks

--Lock

LOCK TABLES tbl_name [as Alias]

--Unlock

UNLOCK TABLES

(Shocker Source: http://www.cnblogs.com/shockerli/p/1000-plus-line-mysql-notes.html)

1000 Line MySQL Learning notes (vii)