12. About MySQL things.

First, what is a thing?

Personal understanding, the SQL is the thing is to do a group of SQL statements, the group of SQL statements or all successful execution, otherwise it will not be fully enforced. Or a series of operations performed as a single logical unit of work, either completely or completely without execution.

Transaction processing ensures that data-oriented resources are not permanently updated unless all operations within the transactional unit are completed successfully. By combining a set of related actions into a single unit that either succeeds or all fails, you can simplify error recovery and make your application more reliable. A logical unit of work is to be a transaction.

Two, examples of things, and how to open things in MySQL.

Start transaction; open transaction

Rollback rolls back the transaction, which is to undo the specified SQL statement (only the INSERT Delete UPDATE statement is rolled back) and roll back to the location of the last commit.

Commit commits the transaction and commits the non-stored transaction.

SavePoint reserved point, temporary placeholder set in transaction you can publish a fallback to it (different from the whole transaction fallback). (It can also be understood that fallback to a point does not completely fall back to the location of the last commit.) ）

Example:

CREATE table test2 (id int PRIMARY KEY auto_increment,name VARCHAR) Engine=innodb;

INSERT into Test2 (name) VALUE ("User1"), ("User2"), ("User3");

Start transaction; #创建事物.

Insert into TEST2 (name) VALUES (' User4 ');

SELECT * from Test2;

Commit #提交后, the real data is written to the database.

-------------------------The following is an example of a retention point-------------------------------

Start transaction;

Insert into TEST2 (name) VALUES (' Wu ');

SavePoint Insert_wu; #定义一个保留点

SELECT * from Test2;

Delete from test2 where id=4;

SavePoint delete1; #定义一个保留点

SELECT * from Test2;

Delete from test2 where id=1;

SavePoint Delete2; #定义一个保留点

SELECT * from Test2;

Rollback to delete1; #回滚到哪个保留点之后.

SELECT * from Test2;

How to invoke a database in Python to start a transaction:

Import Pymysql

#添加数据

conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ', db= ' yyy ')

cursor = Conn.cursor ()

Try

Insertsql0= "INSERT into ACCOUNT2 (name,balance) VALUES (' user1 ', 4000)"

insertsql1= "UPDATE account2 set balance=balance-30 WHERE name= ' User2 '"

Insertsql2= "UPDATE account2 set balance=balance+30 WHERE name= ' User3"

cursor = Conn.cursor ()

Cursor.execute (insertSQL0)

Conn.commit ()

Cursor.execute (insertSQL1)

Raise Exception #触发异常 used to simulate the third SQL statement execution exception.

Cursor.execute (insertSQL2)

Cursor.close ()

Conn.commit ()

Except Exception as E:

Conn.rollback ()

Conn.commit ()

Cursor.close ()

Conn.close ()

Three, the summary of the characteristics of things:

1. Atomic nature:
   

   Atomicity means that a transaction is an inseparable unit of work, and the operations in the transaction either occur or do not occur.

2. Isolation:

   The isolation of transactions means that when multiple users access the database concurrently, the transaction of one user cannot be interfered by other users ' transactions, and the data between multiple concurrent transactions is isolated from each other.

3. Permanent:

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

   
   

The isolation of things is very important!! The following issues occur if you do not consider isolation.

1. Dirty Read (read dirty data)
   

   Dirty reading is a thing read to another thing has not submitted the data, this situation in the production environment particularly dangerous!!

   If you say, now thing 1 is modifying a certain data, and ready to commit (not yet committed!) ), at this time, the thing 2 read to the same data, the thing 1 because the execution of a SQL statement did not succeed, suddenly rolled back, at this time the thing 1 has changed the data to change back to the original value, things 2 read, is the Thing 1 has been modified before the data submitted, so say, Things 2 Read the data and the data in the database is fundamentally inconsistent!! So the data is not correct.

   Examples of dirty reads:

   
   

   A 1000

   B 1000

   A:

   Start transaction;

   Update set money=money+100 where name=b;

   B:

   Start transaction;

   SELECT * From account where name=b;--1100

   Commit

   A:

   Rollback

   B:start transaction;

   SELECT * From account where name=b;--1000

2. Non-repeatable reading:

Thing 1 After reading the data in the database, Thing 2 updates the data, causing things 1 to not read the previous results.

The simple thing to say is to read a row of records in a table, and the content of multiple reads is different.

Transaction 1 After reading a certain data, the thing 2 modifies it, and when the object 1 reads the data again, it gets a different value from the previous one.

3. Generate Phantom Data:

Refers to the data that is inserted into another transaction within a transaction, causing inconsistencies in the read and backward reads. (one transaction reads data that has already been committed by another transaction-adding records, deleting records), is not a problem in a write situation, and in other cases is a problem.

B 1000

C 2000

D 3000

A:

Start transaction

Select SUM (Money) from account;---3000 3000

-------------------

D:start transaction;

Insert into account values (d,3000);

Commit

-------------------

Select COUNT (*) from account;---3 3

3000/3 = 1000 1000

Avoid the isolation levels that occur in these cases:

Four isolation levels:

Serializable: Can avoid dirty reading, non-repeatable reading, the occurrence of false reading. (serialization)

REPEATABLE READ: Can avoid dirty read, non-repeatable read situation occurs. (Repeatable Read) can not avoid false reading

Read Committed: Prevents dirty reads from occurring (Read Committed)

Read UNCOMMITTED: lowest level, none of the above is guaranteed. (Read not submitted)

Security considerations: Serializable>repeatable read>read Committed>read Uncommitted

Database efficiency: Read uncommitted>read committed>repeatable read>serializable

In general, we will use the default database isolation level of the REPEATABLE read, read committed MySQL database repeatable read

Set the Database Isolation LEVEL statement in MySQL:

Set [Global/session] transaction isolation level xxxx;

If you use global to modify the default isolation level of the database, the isolation level of all newly opened windows is inherited from this default isolation level if you use session modification, the isolation level of the current client is modified, regardless of the database default isolation level. What isolation level the current client is, which prevents isolation level issues, is irrelevant to what other clients are isolation levels.

Set the Database Isolation LEVEL statement in MySQL:

SELECT @ @tx_isolation;

This article is from the "Rebirth" blog, make sure to keep this source http://suhaozhi.blog.51cto.com/7272298/1934310

12. About MySQL things.