MySQL study notes 5-MySQL things _ MySQL

MySQL study notes 5-MySQL things bitsCN.com

MySQL transaction ACID indicates that each transaction-type RDBMS must comply with four attributes, atomicity, consistency, isolation, and durability. Through the InnoDB and BDB types tables, MySQL transactions can fully meet the ACID test of transaction security, but not all types support transactions. for example, MyISAM type tables cannot support transactions, transactions can be processed only through pseudo-transactions.

By default, InnoDB tables have the longest durability, and MyISAM tables provide partial persistence. If you want a data table to support transaction processing, you must set the type of the current operational data table to InnoDB or BDB. Create an InnoDB or BDB data table. The basic command structure is as follows:

Create table table_name (field-defintions) TYPE = INNODEB/BDB;

Change Data table type:

Alter table table_name TYPE = INNODB/BDB;

Note: applying the alter table operation may cause data loss in the database. to avoid unexpected results, you must create a TABLE backup before using this command. The general process of creating things: initializing things

Start transaction; create a TRANSACTION

Use the SELECT statement to check whether the data is correctly entered and submitted.

COMMIT undo transaction ROLLBACK1

If you execute a rollback operation, all SQL statements after the START TRANSACTION command will perform the rollback operation. If a transaction is not submitted after it is enabled, the transaction is automatically rolled back by default, that is, no previous operations are saved. The lifecycle of a transaction

Transactions do not support nesting. when a user starts a second transaction after the first transaction is not finished, the previous transaction is automatically submitted. Similarly, many MySQL commands hide and execute the COMMIT command. MySQL automatic submission controls MySQL automatic submission parameters. you can use the AUTOCOMMIT variable to change the submission mode.

Set autocommit = 0;

MySQL submits data in the data table to the database only after you enter the COMMIT command. if you terminate the MySQL session without submitting a transaction, the database will automatically perform the rollback operation.

Use the select @ autocommit; variable to view the isolation level of the currently automatically submitted state.

SERIALIZABLE (serialization)

Repeatable read (REPEATABLE)

Read committed (READ after submission)

Read uncommitted (uncommitted read) command to obtain the isolation level of the current transaction:

SELECT @ tx_isolation; modify the isolation level of a transaction

Set global transaction isolation level;

If you want to modify an isolated level of a transaction, you must first obtain the SUPER priority so that the user can perform the modification operation smoothly. Transactions and performance application small things select the appropriate isolated level to avoid deadlocks MySQL pseudo things use table lock instead of things to add locks for the specified data table

Lock tables table_name lock_type,... you can add, delete, or update some data to the data table. Unlock

Unlock talbes; apply table locks to implement pseudo transactions

BitsCN.com