The default transaction level in sql server 2012 is READ COMITTED. That transaction level uses a pessimistic approach meaning readers can block writers, writers can block writers and writers can block readers.
Is it basically the opposite approach of optimistic approaches like providing row versions i.e. snapshot isolation level.
However in this session we want to focus on how SQL Server maintains consistency in a multi-user environment. The answer is simple: locks.
But how are locks actually handled. Well there is a bunch of information about locking.
First there are locks and latches. Most locks are being held until the end of transactions.
Latches are being held until the specific operation is over. Locks are used to maintain logic consistency. Latches are used to maintain physical consistency.
We will focus on locks for that session.
This session shall just be a basic session so we dont't handle locks in SERIALIZE transaction level. We just focus on the most common ones.
So a lock can be held on a row, an index key, a page or an object (i.e. most likely a table).
There are three basic types of locks.
A shared lock (S) is used whenever data is read. A shared lock is being released when the read is over. That means it is not held until the end of a transaction. There can be numerous shared lock on the same resource from different processes.
An exclusive lock (X) is used when data is changed (UPDATE, DELETE, INSERT). It signalize that the data is exclusively locked for one process. There can only be one exclusive lock on a resource at any given time.
An update lock (U) is used when data is updated. Before the actual data can be updated sql server has to look for the rows that has to be changed. Because in that searching phase we don't want sql server to already exclusively lock the resource, it uses an update lock basically saying i want to exclusively lock this resource later. Therefore there can also be only one update lock on a resource.
There cannot be an exclusive and an update lock on a resource.
To watch current locks on ressources just use the query below:
SELECT * FROM sys.dm_tran_locks WHERE resource_associated_entity_id > 0
Each lock mentioned above has also an Intent version. That means there is an IS, IX and IU lock meaning intent share, intent exclusive and intent update.
So what's the matter with that locks?
Imagine process A has locked a specific row in Table1 because an update is in place. Now process B wants to lock the whole Table1. SQL Server looks into the dynamic management view sys.dm_tran_locks and sees no lock on the table. So now there is a conflict because the table cannot be exclusively locked when still a row is locked for process A.
That is where intent locks come into play. So even process A just locks a single row, there is an intent exclusive lock (IX) on the corresponding page and on the table it self.
Now process B can see the IX lock on the table and therefore has to wait until that lock is released.
I will show an example below in AdventureWorks2012 database:
In a first session execute the following statement:
BEGIN TRAN; UPDATE [Sales].[SalesOrderHeader] SET ShipDate = ShipDate +1 WHERE SalesOrderID = 43666
Now in a second session please execute the statment:
SELECT resource_type, resource_description, resource_associated_entity_id, request_mode, request_status, request_type FROM sys.dm_tran_locks WHERE resource_associated_entity_id > 0
Now we see all three locks mentioned above. Let's try to open a third session that requests a tablelock on the whole table:
SELECT * FROM [Sales].[SalesOrderHeader] WITH(TABLOCKX)
As we can see the third session requested a lock on the table (object) but it was not granted yet. The status is WAIT.
Once our first session COMMIT or ROLLBACK the transaction the WAIT lock for session 3 will be granted.
In the last part i want to talk about the meanings of the output of the dynamic management view.
In the ressource_associated_entity_id we find jsut an entity id for each resource. If resource is an object the id is the object id. You can verify this by using:
SELECT OBJECT_NAME(ressource_associated_entity_id) FROM sys.dm_tran_locks WHERE ressource_type = 'OBJECT'
The column resource_description has different meanings for different resource types:
|OBJECT||object_id in resource_associated_entity_id||26559774|
|PAGE||File number:page number of the actual table or index page.||1:102|
A hashed value derived from all the key components and the locator.
|ROW||File number:page number:slot number of the actual row.||1:159:2|
This was just a basic introduction into sql server locks. In the next session we will talk about details and different types of locks. I will also touch how locks are actually mainted as a data strucure.
Thanks for reading!