17th/24-Week pessimistic concurrency control (pessimistic Concurrency)

Hello everyone, welcome back to performance tuning training. today marks the beginning of the 5th month of training, and this month we will talk about locks, blockages, and deadlocks in SQL Server (Locking, Blocking, and deadlocking).

SQL Server provides pessimistic and optimistic concurrency control patterns, which are used to define the execution of concurrent queries. I'm going to give you a lecture on this issue. Overview of the various isolation levels in the concurrency control model, next week I'll cover the optimistic isolation levels introduced since SQL Server 2005.

pessimistic Isolation level (pessimistic isolation levels)

The pessimistic isolation level means that the read query (SELECT) blocks the Write query (Insert,update,delete), and the write query blocks the read query. SQL Server uses the so-called Lock (Locks)for this behavior:

• Read operation get share lock (shared Locks (S))
• Write operation get exclusive lock (Exclusive Locks (X))

2 locks are incompatible with each other. This means that you cannot read and write a record at the same time. If this happens, there will be a so-called blocking (Blocking) situation. When you set the specified transaction isolation level (transaction isolation levels), you directly affect how the read queries hold their shared locks (S) While the write query is in progress. You can't affect write operations-when you modify a record on a table (Insert,update,delete) always get an exclusive lock (X).

By default, each query runs at the Read Committed isolation level. Read-committed means that SQL Server acquires a shared lock (s) on the record when it reads, and once the record is fully read or processed, the shared lock (s) is released immediately. When you scan the table (single thread), only one shared lock (S) is held at a given time. Because of this behavior, it is possible for other transactions to subsequently modify the record. If you read the same record in the same transaction, you should use the so-called non - Repeatable Read (non-repeatable read): You read a record multiple times, but return different values.

If you can't tolerate repeatable read behavior, you can use the limit to more repeatable reads (REPEATABLE read). This isolation level gives you a repeatable read (because of the name), that is, when you read a record, SQL Server keeps the shared lock (S) until the end of your transaction. So during the transaction that you read, no one can get an exclusive lock (X) to change your record (because of this incompatibility, the exclusive lock will give way to blocking). There are pros and cons to this approach: on the one hand you get a more accurate record (repeatable reading), on the other hand you'll have more blocking happening, because read operations hold their shared locks (S) until the end of their transaction. You need to weigh the concurrency control with the accuracy of the data.

You can also limit further by changing the isolation level to serializable (Serializable) . Use the most restrictive isolation level in SQL Server-you can avoid so-called Ghost Records (Phantom Records). When you get records from a table multiple times, Ghost records appear and disappear. To avoid ghost records, SQL Server uses the so-called Key range Locking technology, which locks the range data you get for the first time.

So there are no other concurrent queries that can insert records within a locked range. It is not possible to delete a record from within a range, or to "move" another record into this range of update statements. Such a query will only block. You also need a supported index on the query predicate where your line scope defines the record. With supported indexes, SQL Server locks the individual index keys. Without a supported index, SQL Server will lock your entire table, which can hurt your database concurrency and workload!

Finally, SQL Server supports the READ UNCOMMITTED isolation level. With commit read, you do not need to acquire a shared lock (S) when reading data. Therefore, it is possible to read uncommitted data from a transaction that is currently in progress. That is called dirty reading (Dirty read). If such a transaction is rolled back, you will read the data that is not logically present in the database. This is not a recommended level of isolation, use the time to consider carefully. Use the famous NOLOCK query hint to force dirty reads.

The pessimistic isolation level is not complicated, is it? The isolation level is how long a data share lock (S) can be held for reading. Based on this, the isolation level defines what can be manipulated and what cannot be manipulated during data read. Look, you'll understand.

Dirty Read (Dirty Read) non-repeatable read (Non-repeatable Read) Ghost record (Phantom Records)

Uncommitted read (READ UNCOMMITTED) Yes Yes Yes

Submit Read (Committed) No Yes Yes

Repeat read (repeatable read) No no Yes

Serializable session (Serializable) No no No

In addition, to ensure the correctness of the query, SQL Server temporarily increases the isolation level for the specified isolation level. You can watch this article: Transaction Isolation Level myth and misunderstanding.

Summary

Today you have learned the basics of each pessimistic isolation level in SQL Server. When you troubleshoot locking and blocking conditions in SQL Server, this is the basis you must know: a read query (SELECT) blocks a write query (Insert,update,delete), and a write query blocks a read query.

Next week we'll talk about the other 2 isolation levels supported by SQL Server using optimistic concurrency control (optimistic Concurrency) combination. Keep your eye on it!

17th/24-Week pessimistic concurrency control (pessimistic Concurrency)