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

This article introduces you how to use nolock in SQLServer2005 database, the content is very detailed, interested friends can refer to, hope to be helpful to you.

How to use SQLServer2005 Database nolock

Concurrent access: multiple users access the same resource at the same time. If any concurrent user modifies the resource, it will have some adverse effects on other users, such as:

1: dirty reading: when a user makes changes to a resource, another user happens to read the modified record, and then the first user abandons the modification and the data goes back to before the modification, the two different results are dirty reading.

2: can not be read repeatedly: an operation of a user is a transaction, and this transaction reads the same record twice. If another user modifies the data after the first read, then the data read the second time happens to be the data modified by other users, which results in different records for the two reads, which can be avoided if the record is locked in the transaction.

3: illusory reading: refers to the situation in which a user reads a batch of records and queries a batch of records under the same condition twice. after the first query, other users have modified this batch of data, possibly by modifying, deleting, adding, and in the second query. It will be found that some of the record entries of the first query are not in the results of the second query, or the entries of the second query are not in the content of the first query.

Why is the nolock flag added after the query table? In order to avoid the adverse effects of concurrent access, SQLServer has two concurrent access control mechanisms: lock and row version control. Adding nolock after the table is one of the solutions to solve the problem of concurrent access.

1 > lock

Each transaction requests a different type of lock for the dependent resource, which prevents other transactions from modifying the resource in a way that may cause a transaction request lock error. When the transaction no longer depends on the locked resource, the lock is released.

Type of lock:

1: table type: lock the entire table

2: row type: lock a row

3: file type: lock a database file

4: database type: lock the entire database

5: page type: lock database pages in 8K units.

There is also a classification of locks, which is divided by user and database object:

1)。 From the point of view of the database system, it is divided into exclusive locks (exclusive locks), shared locks and update locks.

1: share (S): used for operations that do not change or update data (read-only operations), such as select statements.

2: update (U): used in updatable resources. Prevents a common form of deadlock when multiple sessions are reading, locking, and possible subsequent resource updates.

3: exclusive (X): for data modification operations, such as INSERT, UPDATE, or DELETE. Ensure that multiple updates are not made to the same resource at the same time.

2)。 From the programmer's point of view: optimistic lock and pessimistic lock.

1: optimistic lock: rely entirely on the database to manage the lock work.

2: pessimistic locking: programmers manage lock handling on data or objects themselves.

General programmers see what locks and so on, feel particularly complex, of course, the professional DBA is entry-level knowledge. The good news is that programmers do not have to set and control these locks. SQLServer automatically manages lock settings and controls by setting the isolation level of transactions. The lock manager uses the query analyzer to analyze the sql statements to be executed to determine which resources the statement will access and what to do, and then automatically allocate the locks needed for management according to the set isolation level.

2 >: row versioning

When the isolation level based on row versioning is enabled, the database engine maintains the version of each row modified. Instead of using locks to protect all reads, the application can specify that the transaction uses a row version to view the data that exists at the beginning of the transaction or query. By using row versioning, it is much less likely that read operations will block other transactions. That is, it is equivalent to adding nolock to all tables when querying, which will also produce dirty reading, but the difference lies in a unified management place. When it comes to isolation levels based on row versioning, it is necessary to talk about the concept of isolation levels.

The usefulness of isolation levels: control the application of locks, that is, what locking mechanisms are applied in what scenarios.

The ultimate goal: to solve all kinds of problems caused by concurrent processing.

How to use SQLServer2005 Database nolock

Classification of isolation levels:

1: read is not committed, isolating the lowest level of the transaction, only ensuring that physically corrupted data will not be read

2: read submitted, default level of the database engine

3: repeatable

4: serializable; isolate the highest level of transactions, completely isolated between transactions.

Summary: no shared lock is issued when the NOLOCK statement is executed, and dirty reads are allowed, which is equal to the READUNCOMMITTED transaction isolation level. NOLOCK does speed up queries, but it's not without its drawbacks, at least it can cause dirty reading.

Nolock usage scenarios (personal point of view):

1: for tables with a large amount of data, it can be considered to improve performance at the expense of data security.

2: business logic that allows dirty reading, whereas some scenarios with strict data integrity requirements are not appropriate, such as finance.

3: tables whose data is not often modified, which will greatly speed up the query by saving the time of locking the table.

To sum up, if you add nolock after the table of each query in the project, this practice is not scientific, at least it takes a lot of time, and is not as effective as row versioning. And there will be unforeseen technical problems. The most appropriate table should be selectively selected to abandon the use of shared locks.

There are several small differences between nolock and with (nolock):

Synonym in 1.SQLServer2005, only with (nolock) is supported

The writing of 2.with (nolock) is very easy to re-specify the index.

3. When querying statements across servers, with (nolock) can only be used with nolock, while both with (nolock) and nolock can be used when querying on the same server. For example: select* from [IP] .a.dbo.table1with (nolock) this will prompt an error, select*froma.dbo.table1with (nolock) so that you can query successfully.

On how to use nolock in the SQLServer2005 database to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.