When it comes to updating all rows in a table, does the method of locking matter for performance?

Question

Question is a follow up to this.

The SQL in question was

UPDATE stats SET visits = (visits+1)

And the question is, for the purpose of performance, does it matter if you lock all rows in stats in comparison to locking the table stats? Or, if the database uses a page-lock rather than a table/row lock?

Answer 1

In theory you should lock the table, because 1 lock is cheaper than 1M locks.

Many DBs, though, will promote locks for operations like this. As they see the locks expanding, they'll automatically promote to page and table locks.

But, as with anything, "it depends", and it's better to be specific and lock the table yourself.

Edit:

sigh

Postgres example:

LOCK TABLE mytable IN EXCLUSIVE MODE;
UPDATE mytable SET field = field + 1;
COMMIT;

Here's the deal. This is going to happen ANYWAY, the LOCK TABLE command makes it more explicit, and ensures that your intent, locking the table, is clear and capable before the process takes place.

Would I do this on a 10 row table? No.

Would I do this on a database that I KNEW I had exclusive access to? No, there's no need.

Would I do this on a operational database with a table with a large amount a rows? You bet.

Answer 2

There is no predicate on this. Any self respecting DB engine should work this out and realise all rows need updated.

Generally, don't second guess the DB engine: performance is subjectively the same.

Personally,

• I'd not use table or locking hints unless I have to and know why I'm doing it.
• I'd not issue a query like this anyway from an application without a WHERE clause