March 3, 2004 at 12:11 pm
Hi-
I have a question about ROWLOCK and TABLOCK as it related to a SELECT statements performance.
Given I have a simple table called Products, each row having a serial identifier(indexed) and a product name - I have some Join options.
If this table is updated only once in the morning , I would used NOLOCK when joining to other tables to speed things up.
However, if the table is occasionally updated during the day - which hint is better for me and why - TABLOCK or ROWLOCK.
Given I join the table to other tables using the Serial ID field - does it matter - and why ?
If in general I'm searching for one row only passed to a Stored Procedure as the "Product ID" - seems like ROWLOCK is better?
March 3, 2004 at 1:57 pm
In this instance you should not need to provide any locking hints as SQL Server will use the lock with the smallest granularity (likely Rowlock) in this instance.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 3, 2004 at 4:24 pm
I would limit the use of hints if at all possible. Keep in mind that indexes play a role in locking as well, I've seen heap tables prefer to do a table lock and when clustered allow a key lock.
HTH
------------
Ray Higdon MCSE, MCDBA, CCNA
March 4, 2004 at 7:10 am
You said "indexes play a role in locking as well", for better or worse?. As far as I know creating an index will speed up the process of joins. Am I right?
Kindest Regards,
@puy Inc
March 4, 2004 at 7:21 am
Ray thanks - and a few questions.
You said
<I've seen heap tables prefer to do a table lock and when clustered allow a key lock>
1) How do you "See" what locks are being used? What tool allows the "Seeing" of lock types. The QA shows me scan types (Clusterd index seek, scan, etc.) but I see nothing about locking.
2) What is a "Key Lock". Are you really saying "Row Lock" ?
Thanks - B
March 4, 2004 at 7:36 am
If you are testing using Query Analyzer the status pane at the bottom of the active window will tell you your spid. You can then run the system stored procedure sp_lock [spid] to see the locks being held by the process. The out put looks like this:
spid dbid ObjId IndId Type Resource Mode Status
51 1 85575343 0 TAB IS GRANT
YOu can go to BOL and lookup sp_lock to find out wha tall the columns are. The Key columns are TYPE which is the granularity of the lock, MODE which is shared, exclusive, etc.., and STATUS which shows in this case that the lock has been granted to this process, if you saw WAIT you would be blocked by another process.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2004 at 7:55 am
Thanks Jack-
My locks are all DB - I guess meaning the whole database. ACK !!
My performance currently is just fine - nothing operates slowly but why is the whole DB being locked on a simple select from one table?
- B
March 4, 2004 at 9:41 am
The database lock isn't a lock as such, each connection generates a db lock as part of its process.
It's best to leave index hints out - using dirty reads is a valid way to select data without generating locks as long as you don't mind the outside possibility of reading data which is rolled back after your read.
Does you sql use implicit transactions e.g.
begin tran
select xx from xxx
commit tran
if so then you will get read locks.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 4, 2004 at 9:42 am
Any time you are accessing a database in SQL Server using ODBC or ADO the connection will take a Shared lock on the DB. So this should not affect the performance or create a blocking issue. YOu should only see a blocking issue when you are modifying data and then it would be a table lock or smaller, unless you explicity request a lock. Here is a link to an sp that gives some better info on locking than the standard sp_lock, http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2004 at 9:46 am
Thanks..
First to Colin and Jack...
Do you mean Explicit locks? Regardless, for my SELECT statments no - I do not explicitly create any transactions. I just let the system use it's defaults.
Anyway, if ALL my locks are shown as DB - again how the h3ll can I tell what types of locking is going on?
To Jack...
Thanks and I'll review the document now.
- B
March 4, 2004 at 10:13 am
Can't find the url I wanted, sorry, but this may lead you someway forward
http://support.microsoft.com/default.aspx?scid=kb;en-us;224453
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 4, 2004 at 3:49 pm
Billy, query the heck out of books-online and you will learn a lot about locking. I was referring to a key lock, not a row lock, they are very similar but a key lock is supposed to be more optimal than a row lock. First of all, I am not a lock expert but I believe key locks have to do with locks that do not leave the structure, like if you have a nonclustered index that is covering or a clustered index where the query does not leave the table. Again, query the heck out of BOL and if you can, go buy Inside SQL Server 2000 by Delaney.
GL
------------
Ray Higdon MCSE, MCDBA, CCNA
March 4, 2004 at 3:50 pm
Also you can see locks using "sp_lock" or in EM.
------------
Ray Higdon MCSE, MCDBA, CCNA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply