I have been involved in an interesting discussion over the last couple of days around livelocks, and whether or not they exist in SQL Server. The question arose because somebody had been asked the difference between a deadlock and a livelock in a job interview and had not been able to answer the question, and in fact, if you Google livelocks in SQL Server, 90% of the results are sites offering standard SQL Server interview questions.
So first off, what is a livelock? Basically, it is where a resource has an exclusive lock is blocked by a shared lock, but then another shared lock is granted before the exclusive lock, and then another, and another, and so on, meaning that the exclusive lock is never granted.
But do they exist? Well, my initial reaction was no, because SQL's lock manager queues lock requests and grants them in order, and there are not any scenarios where locks can "jump the queue" but I never like to state something as a fact unless I can prove it, and it is very difficult to prove a negative.
However, I though that I would do my best to replicate this behavior, and see what happened. So the first thing I did was create a table and populate it with the following script...
CREATE TABLE livelock (col1 int)
INSERT INTO livelock
VALUES(1)
GO 10000
...The next step was to try and create a live lock, so I opened three query windows and in the first, I ran the following query...
BEGIN TRANSACTION
SELECT col1
FROM livelock WITH (TABLOCK, HOLDLOCK)
...I used TABLOCK to reduce the number of locks, to make it easier to monitor and HOLDLOCK to create a queue.
Then in the second windows I ran...
BEGIN TRANSACTION
DELETE FROM livelock
COMMIT TRANSACTION
...And then in the third I ran the original query...
BEGIN TRANSACTION
SELECT col1
FROM livelock WITH (TABLOCK, HOLDLOCK)
...Now, in a separate query window, I ran a query against the DMV sys.dm_tran_locks, which looks at lock manager, and lets you see the lock waits in progress...
resource_type resource_associated_entity_id request_mode request_type request_status
OBJECT 1927677915 S LOCK GRANT
OBJECT 1927677915 IX LOCK WAIT
OBJECT 1927677915 S LOCK WAIT
...Following this, MVP Tibor Karaszi confirmed that LiveLocks had been removed back in version 6 of SQL Server, so in this context, they no longer exist.
This was where I was planning to end the post, but whilst writing this article, I stumbled across this post by Bob Ward at Microsoft, which talks about the relaxing of the strict queuing within SQL Server from 2005 onwards. However, it also mentions that the logic has been designed to avoid starvation.
So in conclusion, SQL has logic to avoid Livelocks. It may be possible to force one to happen, but fundamentally they are very unlikely to occur.
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Showing posts with label Concurrency. Show all posts
Showing posts with label Concurrency. Show all posts
Thursday, 27 January 2011
Livelocks - Do they exist?
Monday, 24 January 2011
Understanding Transactional Anomolies & Isolation Levels
This isn't going to be a particularly technical post, but I have seen some confusion lately about transactional anomalies, and the isolation levels that protect against them, so I first wanted to cover what the three main anomalies are, and then talk about how isolation levels protect against them.
The worst anomaly is a Dirty Read. This is where a transaction reads data that is subsequently rolled back by another transaction, and hence, never exists in the database, as in the example below. This should never be allowed if business decisions are going to be made, based on this data.
DIRTY READ
The next anomaly is a non-repeatable read. This is where, within the context of one transaction, the same query is issue twice, but the second time it runs the results are different, due to an UPDATE statement in a different transaction, as in the example below. This can cause an issue if you are performing complex calculations such as compound interest.
NON REPEATABLE READ
A phantom read is similar to a non-repeatable read, but relates to the other transaction inserting or deleting data, as opposed to updating it, such as in the example below. This will cause problems if you are performing very complex calculations, such as actuarial calculations.
PHANTOM READ
The worst anomaly is a Dirty Read. This is where a transaction reads data that is subsequently rolled back by another transaction, and hence, never exists in the database, as in the example below. This should never be allowed if business decisions are going to be made, based on this data.
DIRTY READ
Transaction 1
|
Transaction 2
|
BEGIN TRAN
| |
INSERT INTO myTbl(Col1,Col2)
VALUES (1,2)
| |
BEGIN TRAN
| |
SELECT Col1, Col2
FROM myTbl
WHERE Col1 = 1 AND Col2 = 2
| |
COMMIT TRAN
| |
ROLLBACK
|
The next anomaly is a non-repeatable read. This is where, within the context of one transaction, the same query is issue twice, but the second time it runs the results are different, due to an UPDATE statement in a different transaction, as in the example below. This can cause an issue if you are performing complex calculations such as compound interest.
NON REPEATABLE READ
Transaction 1
|
Transaction 2
|
BEGIN TRAN
| |
SELECT Col1, Col2
FROM myTbl
WHERE Col1 = 1 AND Col2 = 2
| |
BEGIN TRAN
| |
UPDATE myTbl
SET Col1 = 2
WHERE Col1 = 1 AND Col2 = 2
| |
COMMIT TRAN
| |
SELECT Col1, Col2
FROM myTbl
WHERE Col1 = 1 AND Col2 = 2
| |
COMMIT TRAN
|
A phantom read is similar to a non-repeatable read, but relates to the other transaction inserting or deleting data, as opposed to updating it, such as in the example below. This will cause problems if you are performing very complex calculations, such as actuarial calculations.
PHANTOM READ
Transaction 1
|
Transaction 2
|
BEGIN TRAN
| |
SELECT Col1, Col2
FROM myTbl
WHERE Col1 = 1 AND Col2 = 2
| |
BEGIN TRAN
| |
INSERT INTO myTbl(Col1,Col2)
VALUES (1,2)
| |
COMMIT TRAN
| |
SELECT Col1, Col2
FROM myTbl
WHERE Col1 = 1 AND Col2 = 2
| |
COMMIT TRAN
|
So how can we protect against these anomalies? Well, the main tool we have at our disposal is transaction isolation levels. Traditionally, SQL Server has supported the following isolation levels:
Read Uncommitteded
Read Committed (DEFAULT)
Repeatable Read
Serializable
But since SQL Server 2005, the following isolation levels have also been provided:
Read Committed Snapshot (Becomes DEFAULT if turned on)
Snapshot
Read Uncommitted does not protect against any of the aforementioned anomalies, and works by not taking out any locks for read operations. It is known as an "optimistic" concurrency level.
Read Committed will protect against dirty reads, but will allow the other anomalies to occur. This level is the default, and works by taking out a lock for read operations, but then immediately releasing it. This is also an "optimistic" concurrency level.
Repeatable Read is a "pessimistic" concurrency level, and is so called because it will stop all anomalies except for Phantom Reads, but works by taking out locks for read operations and holding them for the duration of the transaction.
Snapshot is also a "pessimistic" concurrency level, and works by locking either an index range (if possible) or the entire table.
It goes without saying that if you choose either of the two pessimistic isolation levels, then you will encounter higher levels of contention, meaning more waits and worse...deadlocks!
So to get around this issue, Microsoft introduced Snapshot and Read Committed Snapshot. These isolation levels offer the same level of protection as Serializable and Read Committed respectively, but without taking out any locks!
So that's fantastic isn't it? Lets just use Snapshot for everything right? Wrong! Like everything, there is always a trade-off, and in this case, it relates to how these two isolation levels work. They both work by taking advantage of TempDB called the Version Store.
If you use Snapshot isolation, then at the beginning of every transaction, SQL takes a "snapshot" of all of the required data and puts it in the version store, and satisfies the query(s) from there. This has tow issues. Firstly, you are generating a lot more I/O, so you must ensure that TempDB is properly sized and scaled, and that the I/O sub-system is man enough to handle the extra workload. The second issue is that this could potentially lead to "lost updates" which are not supported by SQL Server. If they were, this isolation level would be aptly called Chaos. This means that if you update the data in the version store, and in the base table at the same time, your snapshot transaction will throw an error and be rolled back.
Read Committed Snapshot also uses the version store, but this time it takes the snapshot at the beginning of every statement within the transaction, rather than at the beginning of the transaction itself. Worse than that, if you turn Read Committed Snapshot on, you are turning off Read Committed, which means that Read Committed Snapshot becomes the default isolation level, and is used for any transactions where an isolation level is not explicitly stated...Watch your server die!
Quite often, if you need a pessimistic isolation level, you just need it for part of a transaction, or for one or two specific tables. If this is the case, rather than using a transaction isolation level, you can use query hints, which give you much more granular control. More information about query hints and how to use them is available in books online.
I hope this cleared things up for anybody who was not familiar with this topic!
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Saturday, 25 December 2010
Contention On The Heap
I was reading a very interesting blog post from Paul Randal, called Lock Logging and Fast Recovery. The theme was log internals, but it set my train of though to how much concurrency you can achieve on a heap, with no non-clustered indexes, and the results may (or may not surprise you).
I created a table called ConcurrencyTest, and populated it, with the following script...
USE Adventureworks2008
GO
CREATE TABLE ConcurrencyTest
(
PKCol int NOT NULL IDENTITY (1, 1),
TextCol varchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE ConcurrencyTest ADD CONSTRAINT DF_ConcurrencyTest_TextCol DEFAULT ('Test Data') FOR [TextCol]
GO
CREATE UNIQUE CLUSTERED INDEX Clust ON ConcurrencyTest(PKCOL)
GO
INSERT INTO ConcurrencyTest DEFAULT VALUES;
GO 10000
...I then picked 2 rows at random, and used %%physloc%% to see what pages the rows were stored on. (See my post What Files and Pages Is My Table Stored On? for more info).
The results below, show that the 2 rows are on separate pages...
...I then opened 2 new query windows, and SQL assigned them the session IDs 53 and 57 respectively.
Next, in each of the query windows, I began an explicit transaction (in order to make SQL hold the locks) and ran an update statement against the 2 rows I choose a moment ago...
Looking at sys.dm_tran_locks, shows that exclusive locks have been taken on the keys, and Intent exclusive locks have been taken on the pages and also the table...
...Again, this is exactly as you would expect. The interesting behavior occurs when all indexes are removed from the table. I dropped the clustered index, rolled back the 2 transactions, and then ran queries again. This time, when I looked at sys.dm_exec_requests, I saw the following results...
...As you can see, this time there is a resource wait on the RID of the first row, even though the where clauses would prevent the same row being updated by both queries.
A look at sys.dm_tran_locks, shows slightly different locks and holds the reason for this contention...
...Instead of locking an index key, SQL has had to take out locks on the RIDs, which are an internal unique identifier found on heaps.
Basically, if there is no clustered index on a table, and the column being updated is not included in the key of a non-clustered index, then SQL has no key to lock and instead locks the RIDs. However, if there is no index, then the data can be stored in any order, which means that a row can move between slots on a page, or if the page is full, could even move to a different page. Therefore, SQL can not allow concurrency across the 2 updates, because although unlikely, there is no guarantee that the 2 updates will not conflict with each other, and SQL Server does not support lost updates.
The moral of the story? Always use indexes....!
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
I created a table called ConcurrencyTest, and populated it, with the following script...
USE Adventureworks2008
GO
CREATE TABLE ConcurrencyTest
(
PKCol int NOT NULL IDENTITY (1, 1),
TextCol varchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE ConcurrencyTest ADD CONSTRAINT DF_ConcurrencyTest_TextCol DEFAULT ('Test Data') FOR [TextCol]
GO
CREATE UNIQUE CLUSTERED INDEX Clust ON ConcurrencyTest(PKCOL)
GO
INSERT INTO ConcurrencyTest DEFAULT VALUES;
GO 10000
...I then picked 2 rows at random, and used %%physloc%% to see what pages the rows were stored on. (See my post What Files and Pages Is My Table Stored On? for more info).
The results below, show that the 2 rows are on separate pages...
...I then opened 2 new query windows, and SQL assigned them the session IDs 53 and 57 respectively.
Next, in each of the query windows, I began an explicit transaction (in order to make SQL hold the locks) and ran an update statement against the 2 rows I choose a moment ago...
(Session 53)...I set both queries running, and a quick glance at sys.dm_exec_requests...
BEGIN TRANSACTION
UPDATE ConcurrencyTest
SET TextCol = 'UpdatedValue'
WHERE PKCol = 1
(Session 57)
BEGIN TRANSACTION
UPDATE ConcurrencyTest
SET TextCol = 'UpdatedValue'
WHERE PKCol = 5000
SELECT session_id, blocking_session_id, wait_resource...showed that there were no blocks (as you would expect). The same would hold true if I dropped the Clustered index and created a non-clustered index on the TextCol column (Although I will let you prove that for yourself)
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
Looking at sys.dm_tran_locks, shows that exclusive locks have been taken on the keys, and Intent exclusive locks have been taken on the pages and also the table...
SELECT * FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'
GO
...Again, this is exactly as you would expect. The interesting behavior occurs when all indexes are removed from the table. I dropped the clustered index, rolled back the 2 transactions, and then ran queries again. This time, when I looked at sys.dm_exec_requests, I saw the following results...
...As you can see, this time there is a resource wait on the RID of the first row, even though the where clauses would prevent the same row being updated by both queries.
A look at sys.dm_tran_locks, shows slightly different locks and holds the reason for this contention...
...Instead of locking an index key, SQL has had to take out locks on the RIDs, which are an internal unique identifier found on heaps.
Basically, if there is no clustered index on a table, and the column being updated is not included in the key of a non-clustered index, then SQL has no key to lock and instead locks the RIDs. However, if there is no index, then the data can be stored in any order, which means that a row can move between slots on a page, or if the page is full, could even move to a different page. Therefore, SQL can not allow concurrency across the 2 updates, because although unlikely, there is no guarantee that the 2 updates will not conflict with each other, and SQL Server does not support lost updates.
The moral of the story? Always use indexes....!
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
Subscribe to:
Posts (Atom)



