Locks in SQL Server

NOLOCK (without lock)

When this option is selected, SQL Server does not add any locks when reading or modifying data. In this case, it is possible for the user to read the data in the incomplete transaction (uncommited Transaction) or rollback (roll back), known as "dirty data."
HOLDLOCK (Hold Lock)
When this option is selected, SQL Server will persist this shared lock to the end of the entire transaction and will not be released on the way.
UPDLOCK (Modify Lock)
When this option is selected, SQL Server uses a modify lock instead of a shared lock when reading data, and holds the lock to the entire transaction or to the end of the command. Using this option ensures that multiple processes can read data at the same time, but only that process can modify the data.
TABLOCK (table Lock)
When this option is selected, SQL Server will place a shared lock on the entire table until the command ends. This option ensures that other processes can read only and cannot modify the data.
Paglock (page lock)
This option is the default option when SQL Server uses a shared page lock when it is selected.
Tablockx (Row it table lock)
When this option is selected, SQL Server will place an exclusive lock on the entire table until the command or transaction ends. This prevents other processes from reading or modifying the data in the table.
HOLDLOCK holds a shared lock until the entire transaction is complete and should be released immediately when the locked object is not needed, equal to the serializable transaction isolation level
NOLOCK statement execution does not emit a shared lock, allowing dirty reads, equal to the READ UNCOMMITTED transaction isolation LEVEL
Paglock with multiple page locks where a table lock is used
READPAST let SQL Server skip any locking lines, perform transactions, apply to READ UNCOMMITTED transaction isolation LEVEL only skip RID lock, skip page, zone and table lock
Rowlock forcing the use of row locks
Tablockx enforces exclusive table-level locks, which prevent any other transactions from using this table during a transaction
Uplock forcing updates to be used when reading tables without sharing locks
Note: The difference between a table that locks a database
SELECT * from table with (HOLDLOCK) Other transactions can read the table but cannot update the delete
SELECT * from table with (Tablockx) Other transactions cannot read tables, update and delete

A simple and multi-order problem can be handled using locks, such as:

BEGIN Tran
Select MAX (bill_no) from Sometalbe with (Tablockx)

... Some processing, if a new ticket is generated, save the data

Commit Tran

Use this kind of transaction must be careful not to appear deadlock!

Here are the articles that cite Bo friends:

Http://www.cnblogs.com/eric_ibm/archive/2012/08/22/lock.html

How to control concurrency and control deadlocks (PB-containing processing points)

Overview of Locks

I. Why to introduce a lock

Concurrent operations on a database by multiple users can lead to inconsistencies in the following data:

Missing updates

A, a, two users read the same data and modify it, and one of the user's modifications destroys the result of another modification, such as a booking system

Dirty Read

A user modifies the data, then the B user reads the data again, but a user cancels the modification to the data for some reason, the data restores the original value, at this time the data obtained by B is inconsistent with the data in the database.

Non-REPEATABLE READ

A user reads the data and then the B user reads the data and modifies it, at which point a user reads the data and finds that the values are inconsistent two times before

The main method of concurrency control is blocking, a lock is a period of time to prohibit users from doing certain operations to avoid inconsistent data

Classification of two locks

There are two kinds of categories of locks:

1. From the database system point of view: divided into exclusive lock (that is, lock it), share locks and update locks

Ms-sql Server uses the following resource lock mode.

Lock mode description

Share (S) is used for operations that do not change or update data (read-only operations), such as a SELECT statement.

The update (U) is used in updatable resources. Prevents common forms of deadlocks that occur when multiple sessions are read, locked, and subsequent resource updates are possible.

The row (X) is used for data modification operations such as INSERT, UPDATE, or DELETE. Ensure that no multiple updates are made to the same resource at the same time.

Intent locks are used to establish the hierarchy of locks. The type of intent lock is: intent sharing (IS), intent Exclusive (IX), and sharing with intent Exclusive (SIX).

Schema locks are used when performing operations that depend on the table schema. Schema locks are of the type: Schema modification (SCH-M) and schema Stability (sch-s).

Bulk Update (BU) is used when bulk copying data to a table and specifying the TABLOCK hint.

Shared locks

A shared (S) lock allows concurrent transactions to read (SELECT) a resource. When a shared (S) lock exists on a resource, no other transaction can modify the data. Once the data has been read, the shared (s) lock on the resource is freed immediately, unless the transaction isolation level is set to repeatable read or higher, or a shared (s) lock is reserved with a lock hint during the lifetime of the transaction.

Update lock

The update (U) lock can prevent the usual form of deadlocks. The general update pattern consists of a transaction that reads a record, gets the shared (S) lock of the resource (page or row), and then modifies the row, which requires the lock to be converted to an exclusive (X) lock. If two transactions acquire a shared-mode lock on the resource and then attempt to update the data at the same time, a transaction attempts to convert the lock to an exclusive (X) lock. The conversion of a shared mode to an exclusive lock must wait for a period of time, because the exclusive lock of one transaction is incompatible with the shared mode lock of other transactions; a lock wait occurs. The second transaction attempted to get an exclusive (X) lock to update. A deadlock occurs because two transactions are converted to exclusive (X) locks, and each transaction waits for another transaction to release the shared-mode lock.

To avoid this potential deadlock problem, use the update (U) lock. Only one transaction at a time can obtain an update (U) lock on the resource. If the transaction modifies the resource, the update (U) lock is converted to an exclusive (X) lock. Otherwise, the lock is converted to a shared lock.

Exclusive lock

An exclusive (X) lock prevents concurrent transactions from accessing resources. Other transactions cannot read or modify data that is locked by an exclusive (X) lock.

Intent lock

An intent lock indicates that SQL Server needs to acquire a shared (S) or exclusive (X) lock on some underlying resource in the hierarchy. For example, a shared intent lock placed at a table level indicates that a transaction intends to place a shared (S) lock on a page or row in a table. Setting an intent lock at the table level prevents another transaction from subsequently obtaining an exclusive (X) lock on the table that contains that page. Intent locks can improve performance because SQL Server examines intent locks at the table level only to determine whether a transaction can safely get locks on the table. Instead of checking each row in the table or the lock on each page to determine whether the transaction can lock the entire table.

Intent locks include intent sharing (IS), intent Exclusive (IX), and sharing with intent Exclusive (SIX).

Lock mode description

Intent Sharing (IS) indicates that the intention of a transaction is to read some, but not all, of the underlying resource in the hierarchy by placing S locks on each resource.

Intent Exclusive (IX) by placing an X lock on each resource, the intention of the transaction is to modify some, but not all, of the underlying resource in the hierarchy. IX is a superset of IS.

Share with intent Exclusive (SIX) by placing an IX lock on each resource, the intention of the transaction is to read all the underlying resources in the hierarchy and modify some, but not all, of the underlying resources. Allows concurrent is locks on top-level resources. For example, the six lock on the table places a six lock on the table (allowing concurrent is locks), and an IX lock on the currently modified page (X lock placed on the modified row). Although each resource can have only one SIX lock for a period of time to prevent other transactions from updating the resource, other transactions can read the underlying resource in the hierarchy by getting the IS lock at the table level.

Exclusive Lock: Only the program that allows the lock operation is used, and nothing else will be accepted for his operation. When you perform a data update command, SQL Server automatically uses exclusive locks. An exclusive lock cannot be added to an object when there are other locks on it.

Shared Lock: A resource with a shared lock lock can be read by another user, but other users cannot modify it, and when a select is executed, SQL Server adds a shared lock to the object.

Update Lock: When SQL Server prepares to update data, it first locks the data object as an update lock, so that the data cannot be modified but can be read. When SQL Server determines that an update data operation is to be made, he automatically changes the update lock to an exclusive lock, which cannot be updated when there are other locks on the object.

2. From the programmer's point of view: It is divided into optimistic and pessimistic lock.

Optimistic lock: The job of managing locks depends entirely on the database.

Pessimistic Lock: The programmer manages the lock handling on the data or object itself.

Ms-sqlserver uses locks to implement pessimistic concurrency control across multiple users who are simultaneously performing modifications within the database

The grain size of the three locks

The lock granularity is the size of the blocked target, the blocking granularity is high concurrency, but the overhead is large, the blockade granularity is large, the concurrency is low but the cost is small.

The lock granularity supported by SQL Server can be classified as a row, page, key, key range, index, table, or database acquisition lock

Resource Description

The RID row identifier. Used to lock a row in a table separately.

The row lock in the key index. Used to protect the range of keys in a serializable transaction.

Page 8,000 kilobytes (KB) of the Data page or index page.

A group of eight data pages or index pages that are adjacent to the extents.

The table includes the entire table, including all data and indexes.

DB database.

The length of the four locking time

The length of time the lock is held is the length of time required to protect resources at the requested level.

The hold time of a shared lock used to protect read operations depends on the transaction isolation level. When using the default transaction isolation level of Read COMMITTED, the shared lock is only controlled during the time the page is read. In the scan, the lock is released until the lock is acquired on the next page within the scan. If you specify HOLDLOCK hint or set the transaction isolation level to repeatable READ or SERIALIZABLE, the lock is not released until the end of the transaction.

Depending on the concurrency option set for the cursor, the cursor can obtain a scroll lock in shared mode to protect the extraction. When a scroll lock is required, the scroll lock is released until the next time the cursor is fetched or closed (whichever occurs first). However, if you specify HOLDLOCK, the scroll LOCK is not released until the end of the transaction.

The exclusive lock that is used to protect the update is not released until the end of the transaction.

If a connection attempts to acquire a lock that conflicts with a lock that is controlled by another connection, the connection attempting to acquire the lock is blocked until:

The conflict lock is freed and the connection acquires the requested lock.

The time-out interval for the connection has expired. There is no time-out interval by default, but some applications set a time-out interval to prevent waiting indefinitely

Five customizations of locks in SQL Server

1 handling deadlocks and setting deadlock priority

Deadlocks are multiple users requesting different blockades, as the applicant has a part of the blockade and waits for a partial blockade that other users have.

You can use set Deadlock_priority to control how the session reacts when a deadlock condition occurs. If two processes lock the data, and until other processes release their locks, each process can release its own lock, that is, a deadlock condition occurs.

2 processing time-out and setting the lock time-out duration.

@ @LOCK_TIMEOUT Returns the current lock time-out setting for the current session in milliseconds

The set LOCK_TIMEOUT setting allows the application to set the maximum amount of time that the statement waits for blocking resources. When the statement waits longer than the LOCK_TIMEOUT setting, the system automatically cancels the blocked statement and returns the 1222th error message for the application that has exceeded the lock request time-out period

Example

The following example sets the lock time-out period to 1,800 milliseconds.

SET lock_timeout 1800

3) Set the transaction isolation level.

4) Use table-level locking hints for SELECT, INSERT, UPDATE, and DELETE statements.

5) Configure the lock granularity of the index

You can use the sp_indexoption system stored procedure to set the lock granularity for indexing

Six-View lock information

1 Execute EXEC sp_lock report information about the lock

2 Information about locks can be seen in Query Analyzer by pressing CTRL+2

Seven usage precautions

How to avoid deadlocks

1 when using transactions, try to shorten the logical processing of the transaction, commit or rollback the transaction as soon as possible;

2 Set the deadlock timeout parameter to a reasonable range, such as: 3 minutes-10 minutes; Over time, automatically abandon this operation, to avoid the process of hanging;

3 Optimize the program, check and avoid the deadlock phenomenon;

4. Carefully test all scripts and SPS before it is the version.

5 All SPS must have error handling (via @error)

6 generally do not modify the default level of SQL Server transactions. Forced lock is not recommended

Fix the problem how to lock the row table database

Eight questions about the lock

1 How to lock a row in a table

SET TRANSACTION Isolation Level READ UNCOMMITTED

SELECT * FROM table rowlock WHERE id = 1

2 locking a table for a database

SELECT * from table with (HOLDLOCK)

Add lock Statement:

Sybase:

Update table set col1=col1 where 1=0;

MSSQL:

Select col1 from table (Tablockx) where 1=0;

Oracle

LOCK table table in EXCLUSIVE MODE;

Locking after the other person is not operational until the lock user unlocked, with a commit or rollback unlock

A few examples to help you deepen your impressions

Set table1 (a,b,c)

A B C

A1 B1 C1

A2 B2 C2

A3 B3 C3

1) Exclusive lock

Create a new two connection

Execute the following statement in the first connection

BEGIN Tran

Update table1

Set a= ' AA '

where b= ' B2 '

WAITFOR DELAY ' 00:00:30 '--wait 30 seconds

Commit Tran

Execute the following statement in a second connection

BEGIN Tran

SELECT * FROM table1

where b= ' B2 '

Commit Tran

If both statements are executed at the same time, the select query must wait for 30 seconds for the update to complete before executing

2) shared lock

Execute the following statement in the first connection

BEGIN Tran

SELECT * FROM table1 holdlock-holdlock manual lock

where b= ' B2 '

WAITFOR DELAY ' 00:00:30 '--wait 30 seconds

Commit Tran

Execute the following statement in a second connection

BEGIN Tran

Select A,c from table1

where b= ' B2 '

Update table1

Set a= ' AA '

where b= ' B2 '

Commit Tran

If both statements are executed at the same time, the select query in the second connection can perform a

and update must wait 30 seconds for the first transaction to release the shared lock to the lock before it can execute

3) Deadlock

Additional table2 (d,e)

D E

D1 E1

D2 E2

Execute the following statement in the first connection

BEGIN Tran

Update table1

Set a= ' AA '

where b= ' B2 '

WAITFOR DELAY ' 00:00:30 '

Update table2

Set d= ' D5 '

Where e= ' E1 '

Commit Tran

Execute the following statement in a second connection

BEGIN Tran

Update table2

Set d= ' D5 '

Where e= ' E1 '

WAITFOR DELAY ' 00:00:10 '

Update table1

Set a= ' AA '

where b= ' B2 '

Commit Tran

While executing, the system detects a deadlock and aborts the process

Add one point:

Table-level locking hints supported by SQL Server2000

HOLDLOCK holds a shared lock until the entire transaction is complete and should be released immediately when the locked object is not needed, equal to the serializable transaction isolation level

NOLOCK statement execution does not emit a shared lock, allowing dirty reads, equal to the READ UNCOMMITTED transaction isolation LEVEL

Paglock with multiple page locks where a table lock is used

READPAST let SQL Server skip any locking lines, perform transactions, apply to READ UNCOMMITTED transaction isolation LEVEL only skip RID lock, skip page, zone and table lock

Rowlock forcing the use of row locks

Tablockx enforces exclusive table-level locks, which prevent any other transactions from using this table during a transaction

Uplock forcing updates to be used when reading tables without sharing locks

Application Lock:

The application lock is the lock generated by the client code, not the lock generated by SQL Server itself

Two procedures for handling application locks

Sp_getapplock Locking Application Resources

Sp_releaseapplock Unlocking an application resource

Note: The difference between a table that locks a database

SELECT * from table with (HOLDLOCK) Other transactions can read the table but cannot update the delete

SELECT * from table with (Tablockx) Other transactions cannot read table, update and delete

1 How to lock a row in a table

A connection is executed.

SET TRANSACTION Isolation Level repeatable READ

BEGIN Tran

SELECT * FROM TableName with (Rowlock) where id=3

WAITFOR DELAY ' 00:00:05 '

Commit Tran

b Connection if the execution

Update tablename set Colname= ' where id=3--wait 5 seconds

Update tablename set Colname= ' where id<>3--can be executed immediately

2 locking a table for a database

SELECT * FROM table with (HOLDLOCK) Note: The difference between a table that locks a database select * from table with (HOLDLOCK)

Other transactions can read the table, but cannot update the delete

SELECT * from table with (TABLOCKX)

Other transactions cannot read tables, updates, and deletes

concurrency control

Concurrency is the ability of multiple users to concurrently access the same data at the same time. General relational databases have the ability to control concurrency, but this concurrency also poses a risk to data consistency. Imagine what would happen if two users tried to access a bank user's record and asked for a change in the user's deposit balance. We can set the DataWindow in PowerBuilder for concurrency control. Concurrency control means that the data is not overwritten or changed in the process of user data modification, and in the following example we will see how to set DataWindow to control development access. To illustrate the problem, let's cite a simple example of a banking system where a user's deposit status is right:

We assume that this is the case: an employee of the company in the bank at the front desk to withdraw 2,000 yuan, the bank cashier inquires the user's deposit information shows the bank deposit balance of 20,000 yuan; At this time, another bank account transfer cheque to pay the account 5,000 yuan, machine query also get current user deposit 20,000 Yuan , when the bank teller saw the user deposit more than the amount of money, the customer paid 2,000 yuan and the user's deposit to 18,000 yuan, and then another operator of the bank in accordance with the cheque, the import of 5,000 yuan Plus, the user's balance changed to 25,000 yuan, So can the database management system accept these changes?

In the design of datawindows, we choose the menu rows| Update ..., the Setup window for the Specify update characteristics appears, where we set the build of the WHERE clause in the UPDATE statement for development control. Here are three options, let's take a look at the results of these three options in this example:

(1) Key Columns: The generated WHERE clause only compares the values of the primary key columns in the table with the same value as the original query to determine which records to modify. In the above example, the operation of the cheque will cover the changes made by the cashier so that the bank loses 2000 yuan.

(2) Key and updateable Columns: The generated WHERE clause compares the values of the primary key column and the modifiable column in the table with the same value as the original query. In the above example two times the results are 20,000 balances, when the first person to modify the balance, the balance is still 20,000 yuan, so the change is established, and the check transfer operation when the balance is not 20,000, so the column does not match, the modification failed.

(3) The key and Modified columns:where clauses compare the primary key and the column that will be modified, in this case the result is the same as the selection of key and updateable Columns, because the balance has changed and is no longer the same as the original query, so it is still not modifiable.

Let's make another assumption, we change the bank's back-office cheque transfer operation to freeze the user deposit, that is, the value of the Status field is changed to frozen, and the event occurs in the following table, then the order in the table 4 ... Can the cashier's changes be established at the front desk:

The 1.Key columns:where clause compares only the primary key values, and obviously the cashier's modifications are allowed.

2.Key and updateable Columns: The resulting WHERE clause includes comparing all modifiable columns, so the cashier modifies the statue field to freeze when the cashier queries the tive, the modification fails, and the error message is displayed.

The comparison of the 3.Key and Modified columns:where clauses includes the primary key and the column to be modified, since the modified column in this column remains unchanged at $20,000, so the cashier's modifications can be established.

In this example, we can see that the key and updateable columns have the most stringent options to avoid the error of modifying the balance when the status column changes, but this also prevents us from making some trilogy centered allowed concurrent modifications, such as the cashier modifying the deposit balance, and the clerk modifies the user's contact address. Therefore, we should choose the appropriate update settings according to the actual situation.

Depending on our use of the database, we have some other options for controlling concurrent access and modification, such as locking data. A lock is a user's action to prevent other users from modifying a specified row. Locks are automatically freed when a transaction such as executing a commit,rollback,disconnect statement is completed. If you are using a DBMS that supports lock operations, the SELECT statement can be added with Holdlock in the FROM clause in the Power-builder DataWindow design: In the SQL window of the Data window, Click the right mouse at the title of the table window and the last option in the pop-up menu is holdlock. Select to generate an SQL statement that locks the queried data after the Re-trievel () function executes to avoid modification access by other users until an event such as Commit,rollback is unlocked. The problem with this approach is that when users are finished querying the data, they may leave the computer for a long time, and no other user can modify the data during this time. In addition, some DBMS, such as Sybase, do not support row-level locks, which means that when you query a row more rows are locked, which adds to the limitations of concurrency processing. Another notable problem is that the transaction commit of one window in a multi-window application will cause the query rows of the other data windows in a transaction to be unlocked, and the modification will likely have an error. Some DBMS systems support a data item called a timestamp (timestamp) to control concurrency. Each table has a timestamp data column that is automatically modified to the current time when the INSERT statement or UPDATE statement modifies the data row. When you want to make a change, the WHERE clause checks to see if the timestamp column matches at the time of the query and when it is modified to ensure that the changes you make do not overwrite others ' modifications, so this confirmation is the same as the key and updateable columns option. Even if two users modify different columns of the same row, the latter will fail. In a common relational database, Sybase and Microsoft SQL Server support the use of timestamps. In PowerBuilder, regardless of what database the user is connected to in the background, as long as the table has timestamp column names and the data type is DATETIME,PB, the option to update characteristics is automatically ignored. Instead, a comparison of the primary key and timestamp columns is generated in the WHERE clause.

If you are using a database that does not support timestamps but supports triggers, you can also add a column of integer columns to the table. When a record in a table is modified, the column automatically adds 1. The following uses the Watcom database, adding a updcnt field to the shipper table and making two triggers so that the field can change when any user or process attempts to modify a row of records.

The Insert trigger is written as follows:

DROP TRIGGER Ins-shipper '

CREATE TRIGGER SHIPPER before INSERT on SHIPPER

Referencing NEW as NewValue

For each ROW

BEGIN

SET NewValue. Updcnt=newvalue. updcnt+1;

END '

Similarly, you can write an update trigger.

In your PowerBuilder application, in addition to the primary key of the table, this column must be added as a detection column in the WHERE clause in the UPDATE statement, so that when the update operation is made, the background database compares the data when it is modified with the user when the retrieve operation is equal. To confirm that it is possible to make changes. In Datawindows, add a updcnt to the unique key column (s) in the lower-right corner of the Specify Update characteris-tics dialog box, noting that the WHERE clause selects key columns , so that PowerBuilder, when constructing the WHERE clause, will assume that updcnt is also the primary key of the table and becomes a detection item.

When the update function of the data window is called, the trigger modifies the UPDCNT list in the record to be the new value, and to ensure that the next modification is valid, you should immediately make retrieve () so that the value updcnt in the DataWindow buffer is the same as the database. It is obvious that the cost of querying immediately after modification is more than any other and

The cost of control is much smaller.

Do not want to do not feel, but really think it seems to be not so clear, my understanding is probably this:

The most stringent concurrency control:
Key and updateable Columns + use Update
Check for primary key and all updatable columns: any one of the columns fails when committed, which prevents the same data from being modified between things.
Direct update mode: Prevents any cross-modification of the primary key column within the object (logic is not correct).

The most relaxed concurrency control:
Key Columns + use Delete then Insert
Check only primary key columns: Allow non-primary key column data to be modified at the same time by different things.
Insert after Delete: Allows logical and correct cross-modification of the primary key column within a thing.

Deadlock Prevention:
The locking mechanisms of different databases vary, but for applications, the most likely cause of deadlocks is that there is no coding habit of checking the execution results of each commit, resulting in a failure to ROLLBACK the deadlock in time when the commit error occurs.

In fact, this is a process, in the DW update, he will first check the number of columns you have set as key and retrieve when the value is the same, if the same update, the difference is reported
"Row changed between retrieve and update" this error
If this is the wrong one, you'll see an UPDATE statement in this error box.
Update table set the column you are setting to be updated =: value
The Where setting key = the original value;

In fact, there are so many, but there are a few cases:
1, when the report is set to: KEY and MODIFIED COLUMNS && use UPDATE
2. Use SQL statement: Add lock or Rowlock.
3, remember to commit or rollback after the update.
4. Use focus or stored procedures to remember to close.

In general, to be simple, remember this:
With DataWindow operation:
Set the Specify Update characteristics for the DW to:
(3) Key and Modified Columns
This way, as long as you update the value of the column is not changed, then everyone can succeed, effectively prevent multi-user overlap update problem, is quite safe, do not have to use the second option.

If you are using SQL statements, use transactions so that you can ensure that your changes are complete and not interfered with by others. And without using the sophisticated methods of the friends above, it would be as simple as this:
Assuming a value of dec ldec_new = 2000
Sqlca.autocommit=false//must be set to not auto-commit
String Ls_err
Dec ldec_xxx
Select Column_xxx into:ldec_xxx from table_yyy where Id=keyvalue; If it is necessary to query the current value.
If Sqlca.sqlcode <0 Then
Ls_err=sqlca.sqlerrtext//Read the error message, then immediately rollback again, so as not to prompt the user is not sure, the table is still locked
Rollback Roll back immediately to prevent other users from waiting.
MessageBox ("Hint", "database has the following error: ~n" +ls_err)
Return
End If

Update column_xxx=:ldec_new from table_yyy where Id=keyvalue; Add and column_xxx if necessary =: ldec_xxx

If Sqlca.sqlcode <0 Then
Ls_err=sqlca.sqlerrtext//Read the error message, then immediately rollback again, so as not to prompt the user is not sure, the table is still locked
Rollback Roll back immediately to prevent other users from waiting.
MessageBox ("Prompt", "The following error occurred while updating data: ~n" +ls_err)
Return
End If

Commit using Sqlca;

That's it, and when you update, the transaction locks up other operations.

Locks in SQL Server