直接把Block翻譯為Block Of Obtain The Lock
直接把Deadlocks翻譯為 Dead Of Obtain The Lock
Locks are used by SQL Server to provide logical transactional consistency.
locks which are held for the duration of the logical transaction .
This is the thing that holds the lock. In a transactional application, the locker is a transaction handle.
For non-transactional operations, the locker is the current thread.
This is the actual data structure that locks the object.
In JE, a locked object structure in the lock manager is representative of the object that is locked.
3.The locked object.
The thing that your application actually wants to lock.
In a JE application, the locked object is usually a database record.
A locker holds its locks until such a time as it does not need the lock any more. What this means is:
The following figure shows a transaction handle,
Txn A, that is holding a lock on database record
002. In this graphic,
Txn A is the locker, and the locked object is record
002. Only a single lock is in use in this operation.
SQL Server has different kinds of lock modes, such as (S) Shared, (U) Update, (X) Exclusive, (I) Intent (Sch) Schema, Bulk Update and Key-Range.
The exclusive lock will be imposed by the transaction when it wants to modify the page or row data, which is in the case of DML statements DELETE, INSERT and UPDATE.
The following table shows the compatibility of the most commonly encountered lock modes.
Latch protects memory on Buffer Pool, Latches are internal to the SQL engine and are used to provide physical memory consistency
Buffer Latches are held only for the duration of the physical operation on the page.
關於”Buffer Pool 寫入 Disk”可以參考:
Basically, Latches are physical locks and hold the lock only for the duration of the physical operation, while the Locks are logical and maintain the lock until the transaction finishes. Both types guarantee data consistency.
A thread of control is blocked when it attempts to obtain a lock, but that attempt is denied because some other thread of control holds a conflicting lock.
Once blocked, the thread of control is temporarily unable to make any forward progress until the requested lock is obtained or the operation requesting the lock is abandoned.
#### Avoiding Blocks
Reducing lock contention is an important part of performance tuning your concurrent JE application. Applications that have multiple threads of control obtaining exclusive (write) locks are prone to contention issues.
Moreover, as you increase the numbers of lockers and as you increase the time that a lock is held, you increase the chances of your application seeing lock contention.
As you are designing your application, try to do the following in order to reduce lock contention:
Another Block Avoiding Design Strategies
Some of the strategies are described here:
“There are a few design strategies that can reduce the occurrence of blocking locks and deadlocks:
A deadlock occurs when two or more threads of control are blocked, each waiting on a resource held by the other thread.
When this happens, there is no possibility of the threads ever making forward progress unless some outside agent takes action to break the deadlock.
#### Deadlock Avoidance
The things that you do to avoid lock contention also help to reduce deadlocks (see Avoiding Blocks).
Beyond that, you should also make sure all threads access data in the same order as all other threads.
So long as threads lock records in the same basic order, there is no possibility of a deadlock (threads can still block, however).
Be aware that if you are using secondary databases (indexes), then locking order is different for reading and writing.
For this reason, if you are writing a concurrent application and you are using secondary databases, you should expect deadlocks.
What resource does a key lock actually lock?
Thanks for contributing an answer to Stack Overflow! Please be sure to answer the question. Provide details and share…stackoverflow.comLocks, Blocks, and Deadlocks
It is important to understand how locking works in a concurrent application before continuing with a description of the…docs.oracle.comLocks, blocks and deadlocks - what's the difference? | Official Pythian® Blog
We are often being paged by development teams talking about locks, blocks or deadlocks and some people make the wrong…blog.pythian.comhttps://blog.sqlauthority.com/2014/03/16/sql-server-what-is-the-difference-between-latches-and-locks/
In this post I'm going to talk about locks on SQL Server. Locks are necessary, they are used in all operations in the…sqlpadawan.comhttps://social.msdn.microsoft.com/Forums/en-US/7fef1dc7-8314-4415-9180-21c837c6c369/why-pagelockkeylock?forum=sqldatabaseengine
SQL-Server–使用另一個Windows-Account操作SSMS-錯誤訊息--請輸入用於cprogram-filesql-server-management-studio-18common7idessmsexe" title="SQL Server–使用另一個Windows Account操作SSMS, 錯誤訊息 : 請輸入用於c:\program file\sql server management studio 18\common7\ide\ssms.exe