Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Unwanted Shared Table Locks Expand / Collapse
Author
Message
Posted Thursday, March 27, 2008 7:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 7, 2012 1:10 AM
Points: 10, Visits: 21
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.
Post #475405
Posted Thursday, March 27, 2008 7:52 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:58 AM
Points: 1,460, Visits: 3,008
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
Post #475414
Posted Thursday, March 27, 2008 8:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 5,383, Visits: 9,954
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
Post #475432
Posted Thursday, March 27, 2008 8:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 7, 2012 1:10 AM
Points: 10, Visits: 21
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
Post #475438
Posted Thursday, March 27, 2008 8:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 7, 2012 1:10 AM
Points: 10, Visits: 21
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.
Post #475442
Posted Thursday, March 27, 2008 8:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:44 AM
Points: 6,731, Visits: 8,476
- 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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #475457
Posted Thursday, March 27, 2008 8:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 7, 2012 1:10 AM
Points: 10, Visits: 21
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.
Post #475463
Posted Thursday, March 27, 2008 9:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:44 AM
Points: 6,731, Visits: 8,476
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 ??

- 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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #475494
Posted Thursday, March 27, 2008 9:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 7, 2012 1:10 AM
Points: 10, Visits: 21
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.
Post #475515
Posted Thursday, March 27, 2008 10:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 5,383, Visits: 9,954
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
Post #475577
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse