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

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.

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.

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.

I've never set a custom lock for something as simple as the above. Are you honestly advocating setting the lock level for a single UPDATE statement? – Evan Carroll Jun 6 '10 at 16:50 Postgres isn't necessarily a good example.It doesn't normally put row locks into a central lock manager (and doesn't have page locks in that sense at all), and locking the table ahead of time doesn't actually change the operation of the update statement at all- much the same for Oracle.

Arguably, this observation just supports your "it depends" answer though :) – araqnid Jun 6 '10 at 17:38 I've heard on good word from a core dev, that this is totally incorrect as applicable to Pg: gist.github. Com/428839 – Evan Carroll Jun 7 '10 at 15:58 @EvanCarroll: that which is totally incorrect? Btw, good suggestion in that log about using ALTER TABLE to rewrite the table.

– araqnid Jun 9 '10 at 22:25 @araqnid, "In theory you should lock the table, because 1 lock is cheaper than 1M locks. " -- In the MVCC even if you lock the table, you have to effectively lock all of the rows inside of it, because locking the rows is done by assigning the xmax of the new row anyway, which is required for any update operation. Locking the table just goes one step further and prevents inserts, and other things that don't have to be blocked by an operation that "locks" all of the rows.

– Evan Carroll Jun 10 '10 at 15:20.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.