Lock record issues during select

When using SQL, you may encounter this problem. When you update a record, you need to use select to retrieve its value or condition, and then use this value to perform the modification operation.

However, when the above operations are put into multi-thread concurrent processing, the problem may occur: When a thread selects a record but has not yet had time to update, another thread may still come in to select the same record.

The general solution is to use the joint mechanism of lock and thing:

For example:

1. Place the SELECT statement in the transaction. Otherwise, the SELECT statement is completed and the lock is released.
2. to block another SELECT statement, you need to apply a manual lock. The SELECT statement uses a shared lock by default. The shared locks between select statements do not conflict with each other. Therefore, if a shared lock is used, even if the lock is not released, the other select statement can share the lock to select data.

Begin tran
Select * from table with (tablockx)

Or select * from table with (updlock, readpast) depends on the specific situation.

Update ....
Commit tran

Lock description:
Holdlock: retains the shared lock until the transaction is completed, instead of releasing the lock immediately when the corresponding table, row, or data page no longer needs it. Holdlock is equivalent to serializable.
Do not issue a shared lock or provide an exclusive lock. When this option is enabled, it may read uncommitted transactions or a group of pages that are rolled back during reading. Dirty reads may occur. Applies only to select statements.
Paglock: a page lock is usually used where a single table lock is used.
Readcommitted: performs a scan with the same lock semantics as the transaction running at the committed read isolation level. By default, SQL Server 2000 operates at this isolation level.
Readpast: Skip the lock row. This option causes the transaction to skip the rows locked by other transactions (these rows are usually displayed in the result set), rather than blocking the transaction, wait for other transactions to release the locks on these rows. The readpast lock prompt is only applicable to transactions that run at the committed read isolation level and read only after the row-Level Lock. Only applicable to select statements.
Readuncommitted: equivalent to nolock.
Repeatableread: Performs scanning with the same lock semantics as transactions running at the repeatableread isolation level.
Rowlock: Use row-level locks instead of page-level locks and table-level locks with a higher granularity.
Serializable: scan with the same lock semantics as transactions running at the serializable read isolation level. Equivalent to holdlock.
Tablock: Use table locks instead of finer-grained row-level locks or page-level locks. Before the statement ends, SQL Server keeps holding the lock. However, if both holdlock is specified, the lock will be held until the transaction ends.
Tablockx uses the table's exclusive lock. This lock prevents other transactions from reading or updating tables and holding them until the statement or transaction ends.
Updlock: Use the update lock when reading the table, instead of using the shared lock, and keep the lock until the statement or transaction ends. Updlock: allows you to read data (without blocking other transactions) and update data later. It also ensures that the data has not been changed since the last read.
Xlock: When the exclusive lock is used and the transaction ends on all data processed by the statement. You can use paglock or tablock to specify the lock. In this case, the exclusive lock applies to an appropriate level of granularity.

 

this article from the csdn blog, reprinted please indicate the source: http://blog.csdn.net/smartyaya/archive/2006/12/01/1423995.aspx