Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the TCL language and DCL language of MYSQL database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, TCL language operation syntax, sometimes one of our requirements (a task), may involve multiple DML (add, delete and modify) operations. Then this series of operations should be seen as a whole and cannot be cut. For example, in an employee system, the data of an employee A may be stored in multiple tables, such as basic information table, business information table, mailbox information table and so on. When we want to delete all the information about this person, in addition to deleting the basic information about this person, we should also delete all other information about this person, such as mailbox, address and so on. Such an operation involves multiple DML statements. Then from the beginning of execution to the actual end, it constitutes a transaction (Transaction). For transactions, we need to ensure the integrity of transactions, can not be divided. The transaction either succeeds or is withdrawn (back before execution), and the part that cannot be executed is terminated.

Note that transactions are involved only when the DML operation is performed. When the mysql database performs every DML operation, the default commit; can use start transaction to cancel the automatic commit.

Commit: used for commit, indicating the successful completion of the transaction. Used to confirm changes to the data, persistence.

Rollback: used for rollback, rollback to before the transaction starts. Cancel all DML operations.

Savepoint: sets the SavePoint for the transaction, which can be rolled back to the specified SavePoint.

Transaction has four basic characteristics, called ACID for short.

(1) Atomicity: that is, the transaction is either successful or withdrawn, and cannot be severed. (2) Consistency: data consistency should be ensured before and after the transaction starts. In the case of transfer, the total amount of money in account An and B before the transfer is 10000. After the transfer, the total amount of money in account An and account B should be 10,000; (3) Isolation: when multiple users are involved in operating the same table, the database opens a transaction for each user. Then while one of the transactions is in progress, the other transactions should be waiting. Ensure that transactions will not be affected. (4) Durability: when a transaction is committed, we want to ensure that the data in the database is permanently changed. Even if the database crashes, we have to ensure the integrity of the transaction.

It is particularly important about the isolation of transactions. When multiple users (multithreading) operate on the same table at the same time, if there is no isolation mechanism, the following problems may occur:

Dirty read: transaction A reads the data that transaction B has just updated, but transaction B rolls back, which causes transaction A to read dirty data, which we call dirty read. For example, when a financial staff updates the accounting report of the company, a 0 is added after the number in the DML statement, but it is not submitted, and then eat, come back from dinner, find errors, and then correct and submit. During the meal, the boss asked the secretary to check the report, and the secretary saw the data with less than zero. This is dirty reading.

Unrepeatable: transaction A reads the same record twice, but transaction B modifies and commits the record between two times, resulting in inconsistent data read by transaction A. The difference between dirty read and dirty read is that transaction A reads dirty data that is not committed by another transaction B, while unrepeatable read is that transaction A reads data committed by transaction B. In most cases, unrepeatable reading is not a problem, because when we query a certain data many times, of course, we have to rely mainly on the results of the final query. But in other cases, problems may occur. For example, the boss asks B and C to check the data of transaction A respectively, and the results may be different. Does the boss suspect B or C?

Illusion: transaction An is modifying the data of the whole table, such as changing all the fields age to 0 years old, and when it is not committed, transaction B inserts or deletes data into the table, such as inserting a piece of data with an age of 25 years old. This causes the data read by transaction A to be inconsistent with the data that needs to be modified, just like an illusion. The similarity between phantom reading and unrepeatable reading: both are for another transaction that has been committed. Difference: non-repeatable reading is for the same record (delete or update the same record), while phantom reading is for a batch of data (insert)

There are four kinds of transaction isolation mechanisms:

1. Uncommitted read (read uncommitted): "dirty data" can be read without isolation control, and unrepeatable or phantom reading may occur. 2. Commit read (read committed): commit read means that you are not allowed to read data that the transaction has not committed. Obviously this level can avoid the problem of dirty reading. But unrepeatable reading and phantom reading may occur. This isolation level is the default isolation level for most databases except mysql. 3. Repeatable read: in order to avoid the problem of non-repeatable reading at the commit read level, put an "exclusive lock" on the qualified records in the transaction, so that other transactions can not modify the data of the transaction operation, which can avoid the problem of unrepeatable reading. Because only operational data is locked, phantom reading occurs when other transactions insert or delete data, and this isolation level is the default isolation level of Mysql. 4, serialization (Serializable), lock the table in the transaction, so that before the end of the transaction, other transactions can not operate on the table data (including adding, deleting and modifying), which avoids dirty reading, non-repeatable reading and phantom reading, and is the safest isolation level. However, because the operation is clogged, it can seriously affect performance.

The relevant syntax formats are as follows:

Check the current isolation mechanism of the database: select @ @ tx_isolation; modify the isolation mechanism of the database: the name of the set transaction isolation level mechanism or the name of the set tx_isolation=' mechanism'; for example: set transaction isolation level read uncommitted or set tx_isolation='read-uncommitted'

II. Operational syntax of DCL language

(1) create a user: create user username@ip identified by newPassword

(2) change the password: alter user username@ip identified by newPassword

(3) display the user's permissions: show grants for username@ip

(4) Authorization statement: grant permission 1, authority 2. On databaseName.* to username@ip

DDL permission name: create, alter, drop, truncate, create view, etc.

DML permission name: insert, delete, update

DQL permission name: select

(5) revoke permissions: revoke permissions 1, permissions 2. On databaseName.* from username@ip

(6) Delete user: drop user username

(7) permission effective statement: flush privileges

This is the end of the content of "what is the TCL language and DCL language of MYSQL database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.