Towards proper SQL transactions (Part 2)

In the previous section , the basics of transaction isolation levels were considered. Here I will try to dig a little deeper and tell with what tools MS SQL Server implements isolation levels.

As you can see in the previous section, there are two ways to maintain isolation:

• Resource Blocking
• Based on the creation of a versioned copy of resources.

The modes based on creating a copy of the data are fairly simple to understand and I think they do not require special attention. If I want to go into the details of their implementation, I can suggest referring to a good description on MSDN . I want to consider how the mechanism based on locks is implemented.

')
The principle of operation of locks will be clear from their classification and description of differences in work. But before we can introduce the first classification, we need to recall some moments of memory organization in MS SQL Server.

Memory organization in MS SQL

All data in the database is divided into pages, which in turn form the extents.

Page

Page - takes 8Kb, the first 96 bytes of which are the title and contain the description of the page.
Page types (differ in the type of information stored):

• Data - data tables (with the exception of columns of variable and very large size);
• Index - indices;
• Text / Image - columns of variable and very large size
• Other types containing supporting information

Extent

An extent is the basic unit of space management (64Kb), containing consecutive 8th pages (8 * 8Kb). There are uniform (Uniform) and mixed (Mixed) extents. Homogeneous contains pages of the same type, and mixed pages - of a different type. As new pages of the same type appear as part of mixed extents, the server tries to create uniform extents, moving pages between mixed extents.
Now, knowing how the memory is organized in the server, you can enter the first classification of locks, namely by the type of the resource being blocked.

Varieties of locks by the type of blocked resources

Blocked resources:

• Specific row in the table
• Key (one or more keys in the index)
• Page
• Extent
• Table and all related data (indexes, keys, data)
• Base (locked when base scheme changes)

Lock escalation

This is a process that is aimed at optimizing server operation, allowing you to replace multiple low-level locks with one or more higher-level locks. For example, if we have created many row level locks, and all the lines belong to one page, the server, in order to save resources, can replace them all with one page level lock.

Varieties of locks by blocking mode

Shared lock

Used for read operations (SELECT) and prevent changing (UPDATE, DELETE) a locked resource. As the name implies, this lock can be combined with other locks (combined or update locks, described below). This means that if a T1 transaction reads data and sets shared locks on the read lines, another T2 transaction can also set locks on the same lines, without waiting for the T1 to release the locks.
Depending on the isolation level of the transaction, the lock can be released as soon as the data is read (Read Committed), or held until the end of the transaction (Repeatable Read and above). You can also hold the lock to the end of the transaction by specifying the corresponding table hints in the request (for example, HOLDLOCK, SERIALIZABLE, etc.)
In the case when locks are released as data is read, they can be released even before the moment the query is completed (SELECT). Those. if we select 10 rows and 10 aligned row level locks are set, then, as soon as the data of the first row is read, its lock is released without waiting for the remaining 9 rows to be read.

Exclusive lock

Used for data modification operations (UPDATE, DELETE). This lock cannot be set if there are any other locks on the resource, i.e. the team will wait for all existing locks to be removed. Being successfully installed, this lock does not allow the installation of new locks of any type, i.e. all requests attempting to access a locked resource will wait for the exclusive lock to be lifted.

Update Block (Update)

To understand why it was necessary to introduce this type of locks, let's take a closer look at the process of updating data. Logically, it can be divided into two stages:

1. Search for update data
2. Update found data.

We already know that in order to update the data correctly, it is necessary to establish an exclusive lock. But, if an exclusive lock is set from the very beginning of the query execution (at the data search stage), we will make it impossible to even read data from other transactions. Therefore, at the first stage (data search) it would be better to establish a shared lock and only if the data is found, then convert it into a exclusive one and make a change. This will allow other transactions to avoid waiting when reading data while the transaction is looking for data to be updated.
It seems everything is fine with the proposed approach. Create a shared lock at the first stage and convert it to a monopole lock on the second. Performance is improved and everyone is happy. But alas, there is a catch here. If, according to the algorithm described above, two different transactions will simultaneously try to update the same data, then we are guaranteed to get a deadlock (Deadlock). Below is how it arises.

Therefore, a new lock mode was needed - update blocking (Update). It behaves as something between a shared (Shared) and exclusive (Exclusive) block. “Monopoly” lies in the fact that there can be only one update block on a resource, and “compatibility” in that at the stage of data retrieval a lock can be combined with other interlocking locks.

Note : update block is used not only for UPDATE operations, but also for deleting data (DELETE) during the search for deleted data. In the case of inserting new rows into a table with a cluster INSERT index, this type of lock is also applicable at the stage of finding the correct position in the index; when a position is found, the update lock (update) is converted to an exclusive index lock, and a new row is inserted.

Locks with intent (Intent)
This type of lock does not represent a special mode. It serves to optimize the operation of the lock installation algorithm described above.
At the core is a simple idea. Before installing low-level locks (row or page level), we always first set the intent lock (Intent) at a higher level — at the table level. If there is no such lock, then we can avoid checking for the presence of existing locks on the resources of interest to us (lines, pages) and immediately install them. If it is, then it is possible to make a more optimal decision about the possibility of installing a certain low level lock. For example, if there is a shared lock with intent, then there is no point in trying to get an exclusive lock at the table level.
Depending on the type of low-level locks, the following types of locks can be distinguished with intent:

• Lock with intent of shared access (IS)
• Locking with the intention of exclusive access (IX)
• Compatible locking with the intention of exclusive access (SIX)
• Lock with intent update (IU)
• Interlocking lock with intent to update (SIU)
• Block updates with the intention of exclusive access (UIX)

Schema Locks

These locks are used to prevent changes to the database structure (tables, views, indexes, etc.) and are divided into two types:

• Schema change lock (Sch-M) - is set when the table schema is updated and for the time of its existence prohibits any access to the table data
• Schema stability lock (Sch-S) - set when executing queries; this lock is added to all schema objects (table schema, indexes, etc.) that are involved in the request and prevents their modification for the duration of their existence

Bulk update blocking

It allows you to support multiple simultaneous bulk data load streams into the same table and, at the same time, prevents access to the table to any other process other than bulk data loading.
Installed using:

• bcp utilities
• BULK INSERT commands

and also if:

• indicated hint TABLOCK
• The “table lock on bulk load) parameter is set using the sp_tableoption stored procedure

Key range lock (Range)

This lock allows you to prevent the problem of phantoms by blocking the range of selected lines. Those. for the selected rowset, it is guaranteed that there will be no new rows (subject to the query criteria), as well as the deletion of rows from the selected rowset. On the basis of this blocking, the isolation level SERIALIZABLE is implemented. More details here .

Lock Compatibility

As mentioned above, some types of locks can be successfully installed on the same resource. Others, on the contrary, will expect completion of all locks. Below is a complete compatibility matrix that shows whether a particular type of lock can be established, if there are already existing locks.
For example, we are interested in whether a shared lock can be set (Shared) if an exclusive lock is set on the resource (Exclusive). To do this, we find the row corresponding to the requested lock (highlighted with a blue frame) and find the value in the corresponding column (highlighted with a red frame). In our example, we see the value “K”, which indicates a conflict, i.e. shared lock (S) will have to wait until the exclusive lock (X) is removed from the resource.

Conclusion

Hopefully, I was able to lift the curtain of the implementation of locking mechanisms and now you have a clear understanding of which isolation levels should be used in various scenarios. After all, only an understanding of the implementation allows to weigh all the nuances associated with the performance of various levels of isolation and, as a result, we can develop a system with the most efficient use of its resources.