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

This article mainly explains "detailed introduction of transactions and locks in sqlserver". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the detailed introduction of transactions and locks in sqlserver".

Dirty reading, unrepeatable reading and phantom reading

(1) dirty reading: dirty reading means that when a transaction is accessing the data and has modified the data, and the change has not been committed to the database, another transaction also accesses the data and then uses the data.

For example:

Zhang San's salary is 5000. In transaction A, his salary is changed to 8000, but transaction A has not yet been committed.

Meanwhile,

Transaction B is reading Zhang San's salary and reading that Zhang San's salary is 8000.

And then

Transaction An encountered an exception and the transaction was rolled back. Zhang San's salary was rolled back to 5000.

Last,

The data read by transaction B with a salary of 8000 is dirty data, and transaction B made a dirty read.

(2) non-repeatable reading: refers to reading the same data multiple times within a transaction. Before the transaction finishes, another transaction accesses the same data. Then, between the two reads in the first transaction, the data read by the first transaction may not be the same because of the modification of the second transaction. This happens that the data read twice in a transaction is different, so it is said to be unrepeatable.

For example:

In transaction A, I read that Zhang San's salary is 5000, the operation has not been completed, and the transaction has not been committed.

Meanwhile,

Transaction B changed Zhang San's salary to 8000 and submitted the transaction.

And then

In transaction A, read Zhang San's salary again, and at this time the salary becomes 8000. The result of two reads before and after in a transaction does not result in unrepeatable reads.

(3) Phantom reading: a phenomenon that occurs when a transaction is not executed independently, for example, the first transaction modifies the data in a table, which involves all data rows in the table. At the same time, the second transaction also modifies the data in the table, which inserts a row of new data into the table. In that case, later, the user who operates the first transaction will find that there are still rows of data in the table that have not been modified, as if there were hallucinations.

For example:

At present, there are 10 employees with a salary of 5000, and 10 people with a total salary of 5000 read by Service A.

At this time

Transaction B inserts a record with a salary of 5000.

This is that transaction An again reads employees with a salary of 5000, with a record of 11. At this time, there is an illusion.

The key point of unrepeatable reading is modification: the same condition, the data you have read, read it again and find that the value is different. The point of phantom reading is to add or delete: under the same condition, the number of records read for the first time and the second time is not the same.

Exclusive lock, shared lock, update lock, optimistic lock, pessimistic lock

1. Two ways to classify locks

(1) from the point of view of the database system, there are three types of locks:

Exclusive lock (Exclusive Lock) the resource locked by an exclusive lock is only allowed by the program that performs the locking operation, and any other operation on it will not be accepted. SQL Server automatically uses exclusive locks when you execute a data update command, that is, the INSERT, UPDATE, or DELETE command. However, an exclusive lock cannot be added to an object when there are other locks on it. The exclusive lock cannot be released until the end of the transaction.

Shared lock (Shared Lock) A resource locked by a shared lock can be read by other users, but other users cannot modify it. When the SELECT command is executed, the SQL Server usually locks the object with a shared lock. Usually, after a data page with a shared lock is read, the shared lock is released immediately.

Update locks (Update Lock) update locks are set up to prevent deadlocks. When SQL Server is ready to update the data, it first makes an update lock on the data object so that the data cannot be modified but can be read. When SQL Server determines that it wants to update the data, it automatically changes the update lock to an exclusive lock. However, when there is another lock on the object, it cannot be updated to lock it.

(2) from the programmer's point of view, locks are divided into the following two types:

Pessimistic lock (Pessimistic Lock), as its name suggests, refers to a conservative attitude towards the modification of data by the outside world (including other current transactions of the system, as well as transactions from external systems), so that the data is locked during the whole data processing. The realization of pessimistic locking often depends on the locking mechanism provided by the database (only the locking mechanism provided by the database layer can really ensure the exclusivity of data access, otherwise, even if the locking mechanism is implemented in this system, there is no guarantee that the external system will not modify the data. Phellodendron mandshurica (Thunb.)

Optimistic locking (Optimistic Lock) adopts a looser locking mechanism than pessimistic locking. Pessimistic locks are mostly realized by the locking mechanism of the database to ensure the maximum exclusivity of the operation. But it is followed by a large amount of database performance overhead, especially for long transactions, which is often unbearable.

The optimistic locking mechanism solves this problem to some extent. Optimistic locks are mostly implemented based on data version (Version) recording mechanism. What is the data version? That is, to add a version identity to the data, which is generally achieved by adding a "version" field to the database table in the version solution based on the database table. When reading out the data, read out the version number together, and then add one to the version number when it is updated. At this point, the version data of the submitted data is compared with the current version information recorded in the corresponding database table, and if the submitted data version number is greater than the current version number of the database table, it will be updated, otherwise it is regarded as out-of-date data. Phellodendron mandshurica (Thunb.)

2. How to use locks in the database

First of all, start with the pessimistic lock. In many other databases such as SqlServer, the data is usually locked by page-level lock, that is to say, the data in a table is a serialized update insertion mechanism, only one piece of data is inserted in the same table at any time, and the other data you want to insert can not be inserted in turn until this piece of data is inserted. The consequence is the decline in performance. When multiple users access concurrently, when a table is operated frequently, it will be found that the response efficiency is very low, and the database is often in a suspended state. On the other hand, Oracle uses row-level locks, which only locks the data you want to lock, and the rest of the data is irrelevant, so there is basically no effect on the concurrency and insertion of data in the Oracle table.

Note: pessimistic locks are more likely to be aimed at concurrency, but optimistic locks are generally sufficient in our applications.

The pessimistic lock of Oracle needs to make use of an existing connection, which can be divided into two ways. According to the difference of SQL statements, one is for update and the other is for update nowait. For example, let's look at an example.

First, create the database table for the test:

CREATE TABLE TEST (ID,NAME,LOCATION,VALUE,CONSTRAINT test_pk PRIMARY KEY (ID)) AS SELECT deptno, dname, loc, 1 FROM scott.dept

Here we use the scott user table of Oracle's Sample to copy the data into our test table.

(1) introduction of for update form

Then let's take a look at how for update locks. We execute the following select for update statement:

Select * from test where id = 10 for update

After locking through this search statement, open another sql*plus window to operate, and then execute the above sql statement, you will find that sqlplus seems to be dead there, as if you can't retrieve the data, but it doesn't return any results, and it just feels stuck there. What is the reason for this time? it is the select for update statement in the first Session that locks the data. Because the locking mechanism here is the state of wait (as long as it does not mean nowait, that is, wait), the current search in the second Session (that is, the stuck sql*plus) is in a waiting state. When the first session has the last commit or rollback, the search result in the second session automatically pops up and locks the data.

However, if your search statement in the second session is as follows: select * from test where id = 10, that is, if there is no statement like for update to lock the data, there will be no blocking.

(2) introduction of for update nowait form

Another situation, that is, when the database data is locked, that is, after executing the sql of for update, what happens after we execute for update nowait in another session.

For example, the following sql statement:

Select * from test where id = 10 for update nowait

Because this statement is designed to use nowait for retrieval, when it is found that the data is locked by another session, it will quickly return an ORA-00054 error indicating that the resource is busy, but specify to obtain the resource in NOWAIT mode. So in the program, we can use the nowait method to quickly determine whether the current data is locked, if locked, we should take corresponding business measures to deal with.

Another question here is what it will be like if we update and delete the data when we lock it.

For example, again, we let the first Session lock the data of id=10, and we execute the following statement in the second session:

Update test set value=2 where id = 10

At this point, we find that the update statement is stuck here just like the select for update statement, and the update will not work properly until your first session release lock. When your update runs, the data is locked by your update statement. At this time, as long as you don't have commit after update, other session still can't lock and update the data, and so on.

In short, the pessimistic lock in Oracle uses Oracle's Connection to lock data. In Oracle, the performance loss caused by using this kind of row-level lock is very small, just pay attention to the program logic, do not cause you to accidentally become a deadlock. Moreover, due to the timely locking of the data, there is no conflict when the data is submitted, which can save a lot of annoying data conflict processing. The disadvantage is that you must always have a database connection, which means that your database connection should be maintained throughout the whole lock to the final release of the lock.

In contrast to pessimistic locks, we have optimistic locks. The optimistic lock also said at the beginning, that is, it is assumed at the beginning that there will be no data conflict, and data conflict detection will be carried out at the time of the final submission.

In optimistic locks, we have three common practices to achieve:

The first is to copy the whole data into the application when the data is obtained, and compare the data in the current database with the data obtained before the update at the beginning of the submission.

When it is found that the two data are exactly the same, it means that there is no conflict to submit, otherwise it is a concurrency conflict, which needs to be resolved by business logic.

The second optimistic approach to locking is to use a version stamp, which is used in Hibernate.

To use a version stamp, you first need to create a new column on the database table where you have an optimistic lock, such as number. each time your data is updated, the number of versions will increase by 1.

For example, there are also two session that also operate on a piece of data. Both take the version number of the current data as 1. When the first session updates the data, you can see that the current version of the data is still 1 at the time of submission, which is the same as the version you got at the beginning. Officially submit, and then increase the version number by 1, at this time the current data version is 2. When the second session also updates the data submission, it is found that the version 2 in the database is inconsistent with the version number taken by the session at the beginning, so you will know that someone else has updated the data, and then carry out business processing, such as Rollback the entire Transaction, and so on.

When using the version stamp, you can use the version stamp verification on the application side or Trigger (trigger) on the database side. However, the performance overhead of the Trigger of the database is still relatively large, so it is recommended not to use Trigger if it can be verified on the application side.

The third approach is a bit similar to the second approach, which also adds a Column for Table, but this time the column is timestamp, which stores the last update time of the data.

After Oracle9i, you can use a new data type, that is, the timestamp with time zone type, to do the timestamp. The data accuracy of this kind of Timestamp is the highest among the time types of Oracle, accurate to microseconds (not yet to the level of nanoseconds). Generally speaking, with the addition of database processing time and human thinking and action time, the microsecond level is very, very enough. in fact, as long as it is accurate to milliseconds or even seconds, there should be no problem.

Similar to the version stamp just now, the timestamp of the data in the current database is checked when the update is submitted and compared with the timestamp obtained before the update. If it is consistent, it will be OK, otherwise it will be a version conflict. If you don't want to write code in a program or cannot write code in an existing program for some other reason, you can also write this timestamp optimistic lock logic in Trigger or stored procedures.

III. Five isolation levels of transactions

The Isolation attribute supports a total of five transaction settings, as described below:

(1) DEFAULT

Use the isolation level set by the database (default), which is determined by the default setting of DBA.

(2) READ_UNCOMMITTED

This is the lowest isolation level for a transaction, and it allows another transaction to see the uncommitted data of the transaction.

There will be dirty reading, non-repeatable reading, and phantom reading (lowest isolation level and high concurrency performance).

(3) READ_COMMITTED

Ensure that the data modified by one transaction is committed before it can be read by another transaction. Another transaction cannot read uncommitted data from that transaction.

Dirty reading can be avoided, but there will be unrepeatable and phantom reading problems (locking the rows being read).

(4) REPEATABLE_READ

Can prevent dirty reading, can not be repeated, but will be unreal read (lock all rows read).

(5) SERIALIZABLE

This is the most expensive but reliable transaction isolation level, and transactions are processed as sequential execution.

Make sure that everything doesn't happen (lock the table).

At this point, I believe you have a deeper understanding of the "detailed introduction of transactions and locks in sqlserver". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.