Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unwanted Shared Table Locks


Unwanted Shared Table Locks

Author
Message
ben.ashton
ben.ashton
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 23
Executing simple SELECT statements e.g. SELECT * FROM CONTACT is giving a customer of ours Table level shared locks instead of Page level shared locks. This is causing major problems as it is leading to other update processes on the CONTACT table are timing out. The DB is SQL 2000

This appears to have started following the customer updating to Service pack 4.

We could not recreate the problem on our copy of their DB until we rebuilt the Unique Clustered index.
Carolyn Richardson
Carolyn Richardson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1557 Visits: 3449
Can you put locking hints on? ie:-
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7519 Visits: 15173
Why is this a problem? If you want to select everything from a table, then you need a lock on the whole table. It doesn't matter whether that's one table lock or several page locks. Probably the best solution would be for you not to use SELECT * FROM queries: only return the columns you need, and use a WHERE clause to filter out the rows that you need, and make sure you have appropriate indexes in order to keep transactions as short as possible. You may consider using the NOLOCK hint or changing the transaction isolation level, but be sure that you understand the implications of this before you do it.

John
ben.ashton
ben.ashton
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 23
Hi Carolyn,

Do you mean

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED?

We are experimenting with this, but I'm still unsure as to why the query optimizer is deciding to use table level locking rather than page level.
I have restored the DB to SQL 2005 and we do not get the problem in 2005
ben.ashton
ben.ashton
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 23
I'm using SELECT * from as a simple example. Doing SELECT SERIALNO, EMAILADRESS FROM CONTACT WHERE.....
gives us the same problem except that the query running speed is much faster and the problem is less noticeable unless you're looking for it.
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6984 Visits: 8839
- how often do you rebuild indexes on your db ?

bad indexes result in IO overhead resulting in longer need for locks.
IO is the slowest part of your server !!

Rebuild all indexes, and your problem may disappear.

Also, provide FK-indexes on your dependant tables ! (unless they really hurt performance)

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
ben.ashton
ben.ashton
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 23
The weird thing is that originally we didn't get the problem on our copy of our customer's database.
....until we rebuilt all the indexes (the Unique Clustered Index on SERIALNO in particular).

It appears that for some reason the query optimizer is deciding to use Table level locking instead of Page level, but we don't understand why.

Running the DB in SQL2005 does not give us the problem.
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6984 Visits: 8839
If volume is increasing, desing flaws may escalate !

is this select * executed from Query analyser or are they using a cursor to walk throug it ?? Ermm

- You may want to use sql profiler and start a trace to figure out what is exactly going on ..
- or use sp_blocker_pss80 (gives connection details)
http://support.microsoft.com/kb/271509/EN-US/

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
ben.ashton
ben.ashton
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 23
We know what is going on in the sense that:

If We run the following in Query Analyser:


SELECT SERIALNO, TITLE, FIRSTNAME, SURNAME FROM
CONTACT WHERE SURNAME LIKE 'A%'

We get a shared table level lock on the CONTACT table

This will block any update process on the CONTACT table

e.g. UPDATE CONTACT SET SURNAME = 'Smith' FROM CONTACT WHERE SERIALNO = '1000'

until the SELECT has completed. Obviously, if the select statement completes quickly then there's not much of a problem as the update will not have to wait long. However, if the SELECT is slow (for instance we have de-duplication routines that use complex where clauses) then we get problems.
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7519 Visits: 15173
Are your statistics up to date? If not, then the query optimiser might not be able to make the correct decision about what type of lock to take for the SELECT statement. For instance, say your statistics were last updated when half of the contact names began with A, but now only one in 26 does. This might be why the locks are being escalated.

John
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search