A preface

This content is about the knowledge of SQL things, mainly including the concept of things, ACID, illusory, non-repeatable, dirty degree and so on;

Learning the basis of this article is the knowledge seeker hair SQL series articles (public number readers directly in the column can be found)

This set of tutorials

  • MYSQL is introduced (1)
  • MYSQL retrieval (2)
  • MYSQL retrieval (3)
  • MYSQL > insert into MYSQL
  • MYSQL things (5)
  • MYSQL data type (6)
  • MYSQL > alter table table_name;
  • MYSQL view (8)
  • MYSQL stored procedure (9)
  • MYSQL cursor and trigger (10)
  • MYSQL > alter table user permissions
  • Introduction to MYSQLl Architecture (12)
  • MYSQL lock wait and deadlock (13)
  • MYSQLl operating json (14)
  • MYSQL Execution Plan (15)
  • MYSQL index (16)

Public account: Knowledge seeker

Inheriting the spirit of open Source, Spreading technology knowledge;

The concept of two things

Transaction refers to a set of atomic SQL operations that guarantee that a set of SQL statements will either be executed or not be executed.

A classic case of bank transfer: small knowledge transfer 100 yuan to small knowledge, the operation steps are as follows

  1. The amount of the bank account is greater than or equal to 100
  2. The bank deducts 100 yuan from the xiaozhi account
  3. The bank will increase the amount of the account by 100 yuan

Suppose there is a money table, the corresponding SQL statement is as follows

  1. begin;
  2. select balance from money where username = ‘xiaozhi’ and balance > = ‘100’;
  3. update money set balance = balance – 100 where username = ‘xiaozhi’;
  4. update money set balance = balance + 100 where username = ‘xiaoshi’;
  5. commit;

The above statement is an atomic operation, begin means to start something, commit means to commit something. If there is no begin or commit and a power outage occurs during statement 3, 100 is deducted from the account value of the small knowledge, but 100 is not added to the account value of the small knowledge. This causes data inconsistency. Stories are dominant in SQL, especially in the amount operation. Learning things must meet 4 conditions (ACID), Atomicity, Consistency, Isolation and Durability.

Three ACID

3.1 atomic

The concept of atomicity is relatively simple, that is, things are divided into a minimum unit, which is indivisible, and all operations in the whole thing either fail or succeed;

3.2 consistency

Consistency means that data is transferred from one state to another without its integrity being destroyed; In the example above, after 100 yuan is deducted from xiaozhi, the amount of xiaozhi account does not increase by 100, which is the data consistency is damaged after state transfer.

3.3 isolation,

Isolation means that after one thing is modified, other things are not visible until the thing is committed. In the example above, the small knowledge account has 500 yuan. After deducting 100 yuan, suppose there is another thing involved, which cannot see the small knowledge account is 400 yuan, it should be 500 yuan;

3.4 persistence

The concept of persistence is simpler: once something is committed, data modification operations are permanently stored in the database; If the server fails, there will be no impact on persistent data;

Simple operation of four things

4.1 Transaction Statements

  • Bengin or start transction means to open things;
  • Commit you can also use commit work to commit things. After committing things, the data is persisted.
  • “Rollback” can also be used to say “rollback work”.
  • Savepoint Identifier, savePoint allows you to create a savepoint in a transaction. A transaction can have multiple Savepoints.
  • Release SavePoint Identifier Removes a transaction savepoint. Executing this statement will raise an exception if no savepoint is specified.
  • Rollback to Identifier Transaction rollback to savepoint;
  • Set transction is used to set the isolation level of transactions;

Begin commit ROLLBACK;

4.2 Isolation level of things

The isolation level of transactions varies in each database storage engine, generally commit reads, but mysql uses InnoDB for repeatability;

  • Read uncommitted(read uncommited) dirty reads occur when the modified statement is not submitted, and the modified contents are visible to other objects. In the example above, 100 yuan is deducted from the small knowledge account 500, and 400 yuan is read by other objects. Therefore, read uncommitted is rarely used in a real production environment
  • Reading has been submitted(read commited) means that one thing can only read data that another thing has already committed; The above example assumes that the amount of xiaozhi account is 500 yuan, the amount of xiaozhi account is deducted by 100, and the amount of Xiaozhi account is added by 100. After the transaction is submitted, another transaction reads the amount of Xiaozhi account 400.
  • Repeatable readRepeatable read means that the data read repeatedly by the same thing is consistent; As the above example, the small knowledge account is 500 yuan, when the above operation is completed, the other thing read n times the small knowledge account is 400 yuan;
  • serializable(Serializable) Add a row of read locks to each row of data, which leads to lock contention and database performance degradation; The highest level of something; It is rarely used in actual production environments;

In mysql, you can use set transction to set the isolation levels of things (read-uncommitted, read-committed, REPEATable -read and serializable)

Mysql 5
select @@tx_isolation;
set tx_isolation = 'read-uncommitted';

Mysql 8 version
select @@transaction_isolation;
set transaction_isolation = ' ';

Copy the code

4.3 Concurrency of things

  • Dirty read: Transaction A reads the data updated by transaction B, and then B rolls back the data
  • Unrepeatable read: Transaction A reads the same data for many times. Transaction B updates and commits the data during the process of reading the same data for many times. As A result, when transaction A reads the same data for many times, the results are inconsistent.
  • Phantom read: A modifies the data in the database, but B inserts A data at this time. When A finishes the modification, it finds that there is still A record that has not been modified. This is called phantom reading. soPhantom reads are for insert statements;

Mysql InnoDB solves the illusory problem through multi-version Concurrency Control (MVCC).

4.3 Actual Operations

A customer list with the following statement

CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `customer_name` varchar(255) DEFAULT NULL COMMENT 'Customer Name',
  `gender` varchar(255) DEFAULT NULL COMMENT 'gender',
  `telephone` varchar(255) DEFAULT NULL COMMENT 'Phone number',
  `register_time` timestamp NULL DEFAULT NULL COMMENT 'Registration Time'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COMMENT='Customer list';
Copy the code

Submit things example

begin;
INSERT INTO `zszxz`.`customer`(`customer_name`, `gender`, `telephone`, `register_time`) VALUES ('Knowledge seeker'.'male'.'9991'.NULL);
commit;
Copy the code

A data entry is added to the database;

Roll back the sample

begin;
INSERT INTO `zszxz`.`customer`(`customer_name`, `gender`, `telephone`, `register_time`) VALUES ('Knowledge seeker'.'male'.'9991'.NULL);
rollback;
Copy the code

No new data is added to the data at this point

4.3 Automatic commit of mysql transactions

In fact, every transaction in Mysql is automatically committed by default.

You can use the following statement to check whether mysql AUTOCOMMIT is enabled

SHOW VARIABLES LIKE 'AUTOCOMMIT'
Copy the code

The following is an example:

Automatic commit can be set with the following statement

  • SET AUTOCOMMIT=0 (0 or OFF disables AUTOCOMMIT)
  • SET AUTOCOMMIT=1

Focus on knowledge seekers: