table locks

Question

table locks

channa wijesinghe

Old Hand

Points: 337
More actions
July 6, 2009 at 2:37 pm

#384924

Hi All,
In a simple example I have 3 tables in a database two tables share a common 3rd table which contains IDs.
(I'm using the star schema part of warehouse design)
here is an example query
Select fsp.*, fsl.* from Fact_SalesPerformance fsp
inner join Dim_Listing dl on dl.ListingID = fsp.ListingID
inner join Fact_SalesLeads fsl on dl.ListingID = fsl.ListingID
where fsp.Date = '7/6/2009'
Now lets say I want to insert new IDs into the Dim_Listing table while running the above query, I will get a lock until the above query completes, then my new IDs are inserted.
My question is what is the best practice to avoid the above scenario or deal with it? Unfortunetly our business doesn't have the luxury of doing a data load at one given time of the day so sometimes the system is loading new IDs while others are pulling data...
If I could minimize the lock time that would be fantastic....
Any help would be great!
thanks
Channa

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

You might want to consider setting your database to READ_COMMITTED_SNAPSHOT. Select queries will not be blocked by INSERT\UPDATE\DELETE operations because it will use row versions to maintain transactional integrity instead of locks.

You should read about snapshot isolation and READ_COMMITTED_SNAPSHOT in SQL Server 2005 Books Online first to make sure you understand what it will do.

channa wijesinghe Old Hand Points: 337 More actions · Answer 2

channa wijesinghe

Old Hand

Points: 337

July 6, 2009 at 3:15 pm

#1020696

Thanks a ton Micheal!

channa wijesinghe Old Hand Points: 337 More actions · Answer 3

So one question concerning READ_COMMITTED_SNAPSHOT, have you ever implemented something like this? It sounds really great only issue is with a growing tempdb and a slight performance hit to inserts, updates and deletes....Is there something I may be missing?

thanks in advance

Channa

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

We are using it. I haven't noticed any negative impact on performance.

It does seem to have eliminated some problems we had with deadlocks.

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 5

1)

When you are running this select the locks are created on the set of rows /Pages /Extents or entire table .This is because you are using * .I do not kow if the number of rows returned are less or more .If its a scan or seek .But looks like you are covering a lot of rows .

Shared locks are not compatible with update or exclusive locks.Hence there is blocking .

2) First , i would recommend you to have the row lock hint implemented .This will create many row level locks instead of page/extent/table level locks .This should then help you not getting your inserts blocked .But there might be performance hit as there will be many smaller level locks .

3) The other solution would be to check if there is a need to create indexes if missing .This will ensure locking for lesser amount of time .

4) finally , yes .READ_COMMITTED_SNAPSHOT isolation level will surely help(over snapshot isolation [check BOL for benefits]).But just see if it supports inserts i.e. selects are not blocked by inserts.

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

channa wijesinghe Old Hand Points: 337 More actions · Answer 6

Hi Guys,

Thanks for the great feedback, this post is ment follow up to how I resolved this issue as to maybe help others in the future

This query is a very dulled down version of the actual query, as to give the viewers a better understanding of the question. Every table has been optimized. Each Table has multiple indexes and statistics that are updated bi-weekly basis. The query has been run through the sql server optimization tool for maximum performance.

Indexes are kept in seperate filegroups which span multiple files as to reduce PageIO locks.

I tried to implement Row level locks but the performance hit was too much. The amount of rows touched was huge.

The READ_COMMITTED_SNAPSHOT was the best bet, at first glance extremely easy to implement, but you should keep in mind the growing tempDB. After the first day of implementation our tempdb had grown from 57 MB to 1.2 GB.

Using some practices in this article:

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1307255,00.html

I created multiple files under the same primary file group of the TempDB, 2 files per Core, put them on a seperate drive with enough diskspace to accomidate rowversioning of all tables in the database.

let me know if you have any questions, concerns

thanks

Channa

bcronce SSCrazy Points: 2597 More actions · Answer 7

Here's a little theory crafting and would be nice if it wouldn't be too hard to try out

Select fsp.*, fsl.* from Fact_SalesPerformance fsp
inner join Dim_Listing dl on dl.ListingID = fsp.ListingID
inner join Fact_SalesLeads fsl on dl.ListingID = fsl.ListingID
where fsp.Date = '7/6/2009'

Ok, the first things I see are the select *, so this means you're pulling back everything

I thought I read somewhere that If you do a select statement and that statement is fully covered by an index, even if there's a write lock, the read query will just scan the index and not get blocked

So, the next part of my theory train of logic was this. You have a WHERE statement checking for a date.

"What If" you cover that column with an index. If reading an index won't get blocked by a write operation, your WHERE statement should read that index and ONLY return committed rows since an uncommited row shouldn't be on the index

after writing all of this, I thought to myself "If there's an index on ListingID for all those tables, this affect should already be occurring". but on the flip side, if the query planner decided to scan Fact_SalesPerformance for that were statement before doing the inner joins, then the WHERE could be table scanning and getting getting hung up on a locked-uncommitted row being inserted