SQL Server performance Terminator-Lock

The concurrency capability of the database should be obvious to all, and the hero is of course a well-known lock.

You can specify a single table reference in select, insert, update, and delete statements.Lock prompt. The system prompts you to specify a Microsoft SQL Server database engine instance for table data lock type or row version control. Table-Level Lock tips can be used to control the lock types obtained by objects in more detail. These lock prompts overwrite the current transaction isolation level of the session.

 

Granularity tips: paglock, nolock, rowlock, tablock, or tablockx.

Isolation level prompts: holdlock, nolock, readcommitted, repeatableread, and serializable.

When the row version control-based isolation level is enabled, the database engine maintains the version of each row modified. ApplicationProgramYou can specify the transaction to use the row version to view the data that exists at the beginning of the transaction or query, instead of using the lock to protect all reads. By using row version control, the possibility of reading operations to block other transactions is greatly reduced.

Update production. Product
With (rowlock)
Set listprice = listprice * 1.10
Where productid = 100; try to add rowlock to your update.

 

Do not apply for a shared lock:

Select * from production. Product with (nolock), try to add nolock to all your queries.

 

Set the isolation level:

Queries with low requirements for database integrity and valid rows:

SET transaction isolation level read uncommitted or SET transaction isolation level read nolock

 

Use paglock or tablock table prompts to enable database engines to use page, heap, or index locks instead of row locks. However, using this option increases the issue of preventing other users from attempting to access the same data. This option should not be used for systems with a large number of concurrent users.

For read operations, use an isolation level that does not generate a shared lock.
When the read_committed_snapshot database option is on, read committed isolation level is used to generate a shared lock ).
Use the Snapshot isolation level (unless the database is being recovered, the snapshot transaction will not request a lock when reading data. The Snapshot transaction that reads data does not prevent other transactions from writing data. The transaction that writes data does not prevent the snapshot transaction from reading data .).
Read uncommitted isolation level. This isolation level can only be used for systems that can operate on dirty reads.