Day42 Things, database locks

The thing is to put some SQL statements as an atomic operation, that is, I will write a few SQL statements, and then I want to put a few SQL statements as a whole, and then let this whole run together, can not be split apart, like we use flour to make a steamed bread, I need to put these flour together as a whole rather than scattered individuals, if you can make steamed bread that these flour on the experiment success, if otherwise failed, become a test, scrap, our SQL statement to achieve this effect, then steamed bread is flour through a certain proportion of water glued together, Our SQL statements need to use a kind of intermediate medium like steamed bread to glue them together as a whole, then this intermediate medium is what we call---------------------things. That's the function and concept of things.

These SQL statements have been glued together as an integral part of things, so if they can run smoothly then they will run together, otherwise any problem will not run successfully.

So it will certainly have its own format,

The first is to create a thing:

Create transaction;

And then we write the SQL statement.

Update count set money=money-800 where id=1;

Update count set money=money+300 where id=2;

The next step is to commit things

Commit

If you do not succeed, you need to use the rollback feature:

Rollback

CREATE TABLE User (IDintPRIMARY Key Auto_increment,nameChar( +), Balanceint) insert into user (Name,balance) VALUES ('WSB', +),('Egon', +),('YSB', +); #原子操作start transaction;update userSetBalance= the whereName='WSB'; #买支付100元update UserSetBalance=1010 whereName='Egon'; #中介拿走10元update UserSetBalance=1090 whereName='YSB'; #卖家拿到90元commit; #出现异常, roll back to the initial state start transaction;update userSetBalance= the whereName='WSB'; #买支付100元update UserSetBalance=1010 whereName='Egon'; #中介拿走10元uppdate UserSetBalance=1090 whereName='YSB'; #卖家拿到90元, an exception did not get Rollback;commit;mysql>Select* fromuser;+----+------+---------+| ID | name | Balance |+----+------+---------+|1| WSB | +||2| Egon | +||3| YSB | +|+----+------+---------+rowsinch Set(0.00Sec

View Code

Here we also need to refer to a concept to more accurately understand things, that is, the problem of space, our things will be created in the buffer in the first place, in theory, every time we execute the operation of MYCQL will produce a thing, as long as in the MYCQL statement each time we run a sentence with a semicolon, The system will feel that we have finished writing a complete SQL statement, and then we will create a thing.

In things, if you do not commit commit action, you create the things left in the buffer, the buffer has a feature is that the data will not be permanently saved, you closed the program after it will be emptied by the system, we have created things after the need to submit things, If some of the SQL statements in the submission process can be executed smoothly but some of them do not work smoothly, then the smooth execution will be put into the buffer, because it is a whole, only partially executed successfully after the commit operation is not possible, so we can only use the rollback function to reclaim it, to reduce the pressure of cache space.

There are 4 characteristics of things:

1. atomicity (Operations within things, either all succeed or all fail)
A transaction is a logical unit of work for a database, and each operation contained in a transaction is either done or not

2, consistency (before the thing, before and after the consistency of data)
The result of the transaction execution must be to change the database from one consistent state to another. Therefore, when the database contains only the results of a successful transaction submission, it is said that the database is in a consistent state if the database system is running in a failure, some transactions have not yet completed the forced interruption, these outstanding transactions to the database modifications have been written to the physical database, the database is in an incorrect state, Or a state of inconsistency.

3, Isolation (multiple things, can not interfere with each other)
The execution of a transaction cannot be disturbed by other transactions. That is, the operations within one transaction and the data used are isolated from other concurrent transactions, and the transactions performed concurrently cannot interfere with each other. (If things in the execution of the concurrent things are also executing the same SQL statement, that is, the same source file, then the card master, the first execution of the thing is not finished, the next thing to execute the same source file will be the card master, and so on, until the first execution of the things submitted or rolled back, it will go to execute, This concept is a bit like the Gil lock inside the network programming, and someone who takes it will need to wait until someone releases it, and other people can grab it and execute their own program.

4. Persistence (Once a commit has been committed, it cannot be rolled back)

Also known as permanence, when a transaction is committed, its changes to the data in the database should be permanent. The next operation or failure should not have any effect on its execution results. (once submitted, the results are taken out of the cache and put into the hard disk, that is, the physical area corresponding to the buffer, hard disk storage has a big feature is that the data will be stored permanently)

We also need to refer to the function and concept of a lock to come in,

Our database access is to support concurrency, while many people can access it, then there is a problem at the same time access, such as a want to query this data, and then B want to delete this data, then there will be a failure, this conflict, in order to resolve the conflict, we need a lock to resolve the conflict, Make all the accesses sequential so that you can avoid a similar situation.

In the practical process, if more than one user accesses a data at the same time and then is a functional query function, then there will be no conflict, only in the modification and deletion of the conflict exists, we in order to increase productivity and achieve concurrency performance, there are two locks to solve this problem, pessimistic lock, optimistic lock,

3. Two types of locks common in development:

3.1 Pessimistic lock (pessimistic lock), as the name implies, is very pessimistic, every time to take the data when they think others will change, so every time when the data are locked, so that other people want to take this data will block (block) until it gets the lock. Many of these locking mechanisms are used in traditional relational databases .

3.2 optimistic Lock (optimistic lock), as the name implies, is very optimistic, every time to take the data when they think others will not be modified, so will not be locked, but in the update will be judged in the period when others have to update this data, you can use the version number mechanism. Optimistic locking is useful for multi-read application types, which can improve throughput, such as the fact that a database provides an optimistic lock similar to the write_condition mechanism.

2.1 Shared Locks ( Shared Lock , also called S Lock)

A shared lock (S) represents a read operation on the data. Therefore, multiple transactions can share a lock on an object at the same time. (If the door of the fitting room is not locked, the customer can go to visit at the same time)

2.2 Exclusive Lock (Exclusive lock, also called X - lock )

An exclusive lock is also called a write lock.

An exclusive lock indicates that the data is written. If a transaction has an exclusive lock on the object, the other transaction cannot add any more locks to it. (a customer locks the fitting room from the inside, and the other customer wants to use the fitting room, only to wait for the lock to open from the inside)

Two kinds of locks have advantages and disadvantages: do not think one is better than the other, such as optimistic lock for less write, that is, the conflict really rarely occurs, this can save the lock overhead, increase the overall system throughput. However, if there is frequent conflict, the upper-level application will continue to retry (retry), which is to reduce the performance, so in this case, pessimistic locking is more appropriate.

When we use locks, we also have a fixed format, which is a fixed format for optimistic locking.

--The concept of optimistic locking

You need to create a table first,

You need to set up a field in the table, version, and give him a default of 0. The following SQL statement is bound as follows

--ALTER TABLE count add version int default 0

Here we begin to use the optimistic lock:

SELECT * from count where id=2;

You must query the table when you modify it, which is a prerequisite.
Update Count Set money=money-30,
Version=version+1 where id=2 and version=1; ---Here we make changes to the results of the query when the fixed format, to be based on the version of the condition to be modified, because every time the query version will change, so each time the modification will need to manually change the value of the version here, It's going to be a bit of a hassle. But it saves the cost of the lock and increases the throughput of the entire system (the process of constantly retrieving data from the database as it is being queried and then continuously outputting the results of the query) is more efficient.

Again, it's a pessimistic lock. Fixed format:

--Start transaction;
--select * from Count where id=2 for update;
--Update count set money=2000-200 where id=1;
--commit;

This is the use of pessimistic lock format, and the same thing. Because there is a concept of lock in the thing, in order to avoid the existence of the conflict, it will let the multiuser access the data of the same source file in sequence at the same time, which is the implementation of pessimistic lock.

Day42 Things, database locks