December 20, 2011 at 2:08 am
Hello,
As a newcomer to SQL and still stumbling my way through it, I came across a situation while testing how locking is implemented in SS2008 and have come across a situation that I would like some help with please.
I have a simple table structure with lots of rows in it that I use for testing and general playing around while learning. This table has an IDENTITY column that is the primary key. One of the other columns is a DATE column and I wanted to update a specific range of rows based on a date range.
I issued the following statement:
BEGIN TRANSACTION
UPDATE TempSales
SET SalesFlag = ~SalesFlag
WHERE SalesDate BETWEEN '2011-06-01' AND '2011-06-30';
While this update was running I issued the following statement in another query window:
SELECT SalesFlag
FROM TempSales
WHERE SalesDate BETWEEN '2011-07-01' AND '2011-07-31';
The above query was blocked by the execution of my first update and didn't commence until I issued a "COMMIT" after the UPDATE statement had completed.
I puzzled at this because I thought that given the date ranges of the UPDATE and SELECT covered different row sets that blocking of this sort would not occur.
It then occurred to me that perhaps I need to make the "SalesDate" column an index in order for the UPDATE and SELECT statements to be able to use and therefore not have to scan the table sequentially until the relevant row set is encountered. However, adding the index did not make any difference.
It appears that a table lock of sorts is held even though a sub-set of rows are actually updated. Now I know I can set the TRANSACTION ISOLATION LEVEL for my SELECT query to READ UNCOMMITTED, but is this the only method of bypassing locks on specific rows?
Can you not have SQL Server deny access to only those rows affected by the update rather than the entire table?
Apologies if my question slaps of pure ignorance, but I am still a total novice.
Thanks for any help or advice.
Regards
Steve
December 20, 2011 at 2:13 am
Search for snapshot isolation level.
Isolation Levels in the Database Engine
December 20, 2011 at 2:20 am
Dev (12/20/2011)
Search for snapshot isolation level.Isolation Levels in the Database Engine
Thank you.
I will take a look at this although I have heard of snapshot isolation, I do believe this was only introduced in SS2005 and did not exist prior to this version.
Could what I was asking about have been even possible in SQL Server 2000? i.e table lock or nothing ?
December 20, 2011 at 2:39 am
Hints
http://msdn.microsoft.com/en-us/library/aa196160(v=SQL.80).aspx
ROWLOCK
Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.
December 20, 2011 at 2:47 am
SQL will take locks at the lowest granularity it can. It depends on how many rows SQL thinks will be affected. If its a small number, row locks are taken. A larger number will cause page locks and even larger full table locks. It's a trade off between resources and concurrency. It takes memory to hold locks, so queries that need a lot of rows will get higher locks (page or table).
Locks can also escalate. SQL can start with row or page locks and, if too many locks are taken, escalate to table.
Is there an index on SalesDate? If not, SQL will have to read the entire table for that and may well take a table lock. Also, considering how close the ranges are, if there was an index and SQL took page locks, it could well be that a lock on the page had one of the rows that the second query needed.
As for getting around it. First thing is to keep transactions as short as possible. So Begin.. update .. commit/rollback. Leave transactions open for a log time and you'll have more than locks causing problems. Make sure that the queries are as optimal as possible (properly written, good indexes)
If you've done all that and still have locking problems and are on 2005 or 2008, then consider either read committed snapshot isolation or snapshot isolation. They're both isolation levels that don't use locks for readers (writers, like updates, still take locks). Be careful of the tempDB impact.
Read committed snapshot is an optimistic version of read committed. Snapshot isolation is an optimistic version of serializable isolation. Read up on the differences, see which, if either, you need.
Read uncommitted is to be avoided if possible, it has some interesting effects on data returned (can be wrong, repeated or missing), so use with caution.
Chapter 6 of this is all abut blocking and there's a large section on lock modes, lock granularities and isolation levels. http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 2:50 am
One thing to note about the locking hints is that they specify the minimum, not the actual that will be used. You can specify rowlock and still get a table lock at the end of the day. You can specify UPDLock and still get an exclusive lock if SQL decides it's necessary.
Better to understand the reasons why you're getting certain locks and work on resolving any problem than replying on hints (which should be a last resort)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 3:02 am
Absolutely perfect reply! 🙂
As a novice, I do find a reply like yours most helpful as it boilds down the technically written articles in some of the links provided.
I did index the SalesDate column with the same result. As I understand it, a "page" is an 8K block of data that can encapsulate more than one row of data from a table and as such a lock can effectively be held on more than one row even though those rows are not all included in an UPDATE.
I will check out the link you kindly provided. it sounds just like the sort of site I'm looking for that might explain things in more simple language and doesn't assume you have a PhD in quantum theory! 🙂
December 20, 2011 at 3:20 am
raotor (12/20/2011)
As I understand it, a "page" is an 8K block of data that can encapsulate more than one row of data from a table and as such a lock can effectively be held on more than one row even though those rows are not all included in an UPDATE.
Exactly
I will check out the link you kindly provided. it sounds just like the sort of site I'm looking for that might explain things in more simple language and doesn't assume you have a PhD in quantum theory! 🙂
Not a site, it's a book. It's had rave reviews and I'm not going to add to them (you may realise why when you see the book)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply