從標題我就故意把Locks與Latch放在一起，Blocks與Deadlocks放在一起，因為他們就是這樣倆倆相似的。

Locks與Latch他們都是為了處理Data Consistency(一致性).

Blocks與Deadlocks則是因為有了Locks與Latch而產生的.

Block中文為阻塞,在Database世界

直接把Block翻譯為Block Of Obtain The Lock

直接把Deadlocks翻譯為 Dead Of Obtain The Lock

這樣就非常好理解了!

Lock是前因，而Block是後果。

(此後果並無貶意只代表順序,他們都因處理Data Consistency而生,只要沒有太多,都是正常的).

Lock: Transaction的[邏輯]鎖

Locks are used by SQL Server to provide logical transactional consistency.

locks which are held for the duration of the logical transaction .

所謂Lock同時包含了以下3種東西

1.The locker.

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.

2.The lock.

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.

Lock Lifetime

A locker holds its locks until such a time as it does not need the lock any more. What this means is:

1. A transaction holds any locks that it obtains until the transaction is committed or aborted.
2. All non-transaction operations hold locks until such a time as the operation is completed. For cursor operations, the lock is held until the cursor is moved to a new position or closed.

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.

#### Lock類型

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.

• (S) Shared lock is used in read operations.
• (U) Update to avoid potential deadlock problem.
• (X) Exclusive prevent access to a resource by concurrent transactions.

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.

• (I) Intent prevent other transactions from modifying the higher-level resource and improve the efficiency of the Database Engine in detecting lock conflicts at the higher level.
• (Sch) Schema uses schema modification (Sch-M) locks during a table data definition language (DDL) operation.

The following table shows the compatibility of the most commonly encountered lock modes.

#### 減少Lock的策略:

在Query已經足夠簡短(比如一個簡單的SELECT Query)且不改變一致性原則下,我們無法單純對Lock進行改善

在Query已經足夠簡短(比如一個簡單的SELECT Query)且可改變一致性原則下,能夠使用查詢提示改變一致性,減少Lock.

而其他情況下,減少Lock的策略與減少Block及其他資料庫優化手段相同(Block策略列在下方)

Note：
什麼是KeyLock? 一種索引行鎖.

參考:
Why Pagelock/Keylock?
What resource does a key lock actually lock?

Latch: Transaction的[實體記憶體]鎖

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”可以參考:

[SQL Server]記憶體緩存資料寫入磁碟(一)首部曲

[SQL Server]記憶體緩存資料寫入磁碟(二)自動與間接檢查點(Auto and Indirect CheckPoint)

[SQL Server]記憶體緩存資料寫入磁碟(三)延遲持久性Delayed Durability(和魔鬼交易)

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.

#### 減少Latch策略:

由於是與記憶體相關的,我們更沒有什麼可以直接減少的方式.

與減少Lock其他情況一樣,減少Latch的策略與減少Block及其他資料庫優化手段相同(Block策略列在下方)

Block:Thread阻塞獲得鎖(The Lock not the locked object!)

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:

• Reduce the length of time your application holds locks.
• Shorter lived transactions will result in shorter lock lifetimes, which will in turn help to reduce lock contention.
• In addition, by default transactional cursors hold read locks until such a time as the transaction is completed. For this reason, try to minimize the time you keep transactional cursors opened, or reduce your isolation levels — see below.
• If possible, access heavily accessed (read or write) items toward the end of the transaction. This reduces the amount of time that a heavily used record is locked by the transaction.
• Reduce your application’s isolation guarantees.
• By reducing your isolation guarantees, you reduce the situations in which a lock can block another lock. Try using uncommitted reads for your read operations in order to prevent a read lock being blocked by a write lock.
• In addition, for cursors you can use degree 2 (read committed) isolation, which causes the cursor to release its read locks as soon as it is done reading the record (as opposed to holding its read locks until the transaction ends).
• Be aware that reducing your isolation guarantees can have adverse consequences for your application. Before deciding to reduce your isolation, take care to examine your application’s isolation requirements. For information on isolation levels, see Isolation.
• Consider your data access patterns.
• Depending on the nature of your application, this may be something that you can not do anything about. However, if it is possible to create your threads such that they operate only on non-overlapping portions of your database, then you can reduce lock contention because your threads will rarely (if ever) block on one another’s locks.

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:

• Use clustered indexes on high-usage tables.
• Avoid high row count SQL statements that can cause a table lock. For example, instead of inserting all rows from one table to another all at once, put a single INSERT statement in a loop and insert one row at a time.
• Break long transactions up into many shorter transactions. With SQL Server, you can use “bound connections” to control the execution sequence of the shorter transactions.
• Make sure that UPDATE and DELETE statements use an existing index.
• If you use nested transactions, be sure there are no commit or rollback conflicts.

My additions:

• Access objects always in the same order (i.e.: update Table1, Table2 and Table3 rather than sometimes Table2 first).
• Don’t schedule long data updating processes to run concurrently, if possible.
• Keep transactions as short as possible.

Deadlocks:Thread死阻塞獲得鎖(The Lock not the locked object!)

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/

https://dotblogs.com.tw/stanley14/2017/02/01/205401https://dotblogs.com.tw/stanley14/2017/02/02/233654https://dotblogs.com.tw/stanley14/2017/02/06/212703

Locks everywhere
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

• Tags:
• SQL Server

• ← AlwaysOn 如果備援與唯讀同一台會怎麼樣? - SQL Server
• Log Backup Frequency 策略 - SQL Server →