Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I just started to read about table lock in SQL (from this page)

It saids:

table lock may reduce the overhead of acquiring a large number of row or page locks and save overall locking time

From my understanding it will prevent other query to access the table I have locked, but why will this improve the performance? Isn't it will slow down over roll queries when there is mutiple user trying to use the table? Can someone explain little bit more?

share|improve this question
What RDBMS are you actually on? I see you didn't add the sql-server tag yourself and you link to Sybase docs. – Martin Smith Dec 29 '11 at 19:04
I am using SQL-Sever, but I thought table lock is same concept on all RDBMS? Or.. I am wrong? Still learning a lot of stuff..... – King Chan Dec 29 '11 at 19:09
Doubt answers would be much more different but knowing specific RDBMS perhaps allows a bit more specific detail and was just wondering if the retag was appropriate. – Martin Smith Dec 29 '11 at 19:12

migrated from stackoverflow.com Dec 29 '11 at 19:41

3 Answers

up vote 1 down vote accepted

In a multi-user environment, you'll want to avoid table locks as it is bad for concurrent use and prone to deadlocks.

If you do large bulk DML with lots of updates/deletes in one transaction however, you will avoid the overhead of the database server having to manage locks on the row or page level if you end up modifying a large percentage of the table anyway.

share|improve this answer
I see, so it talks about the performace improve for one large tranaction. hmm, even if I do not use table lock, the database server will put a lock on the row when I try to modify the table? – King Chan Dec 29 '11 at 19:13
if you are lucky, a row lock yes. some database servers do page locking (4k memory pages) instead. if it can't find the relevant row quickly (because no index is available for example) some databases resort to full table locks as a last resort. – bcolyn Dec 29 '11 at 19:17
Thanks, now I see how it works. I thought lock table is only for pervent changes on same row/data on table (I was thinking in a way sharing a object in muti-threading using lock). – King Chan Dec 29 '11 at 19:24
up vote 4 down vote

Let's say you have a million row table.
One million individual row locks is a lot of memory that can be put to better use.

I'd also read some SQL Server articles on lock escalation:

Similarly, if you lock individual rows, you will get higher concurrency but then you will incur the overhead of acquiring/releasing locks on each row and lot more locking resources depending upon the isolation level of your transaction, as you may need to hold the locks on all the rows till the end of transaction.

share|improve this answer
up vote 1 down vote

Read your quoted passage more closely; it's not talking about overall performance (though that often does apply as a secondary effect), it's talking about saving "overall locking time" when doing a whole table lock, instead of doing large numbers of row or page locks.

Yes, that could prevent other queries from access, but so could the large number of row/table locks. And this gets the whole business of doing the lock down to less time/effort/resources, since it's only locking a single object.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.