Thread: Locking in PostgreSQL?
Does PostgreSQL lock the entire row in a table if I update only 1 column? -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl
Unless you specifically ask for it postgresql doesn't lock any rows when you update data. Dave On 6-Dec-06, at 2:04 AM, Joost Kraaijeveld wrote: > Does PostgreSQL lock the entire row in a table if I update only 1 > column? > > > -- > Groeten, > > Joost Kraaijeveld > Askesis B.V. > Molukkenstraat 14 > 6524NB Nijmegen > tel: 024-3888063 / 06-51855277 > fax: 024-3608416 > web: www.askesis.nl > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
On Wed, 06 Dec 2006 13:29:37 +0100, Dave Cramer <pg@fastcrypt.com> wrote: > Unless you specifically ask for it postgresql doesn't lock any rows when > you update data. > Thats not right. UPDATE will force a RowExclusiveLock to rows matching the WHERE clause, or all if no one is specified. @Joost Kraaijeveld: Yes, because there is no EntryExclusiveLock or something like that. Roughly you can say, each UPDATE statement iterates through the affected table and locks the WHERE clause matching records (rows) exclusivly to prevent data inconsistancy during the UPDATE. After that your rows will be updated and the lock will be repealed. You can see this during an long lasting UPDATE by querying the pg_locks with joined pg_stats_activity (statement must be enabled). > Dave > On 6-Dec-06, at 2:04 AM, Joost Kraaijeveld wrote: > >> Does PostgreSQL lock the entire row in a table if I update only 1 >> column? >> >> >> --Groeten, >> >> Joost Kraaijeveld >> Askesis B.V. >> Molukkenstraat 14 >> 6524NB Nijmegen >> tel: 024-3888063 / 06-51855277 >> fax: 024-3608416 >> web: www.askesis.nl >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 9: In versions below 8.0, the planner will ignore your desire to >> choose an index scan if your joining column's datatypes do not >> match >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match CU, Jens -- ** Jens Schipkowski
On 6-Dec-06, at 8:20 AM, Jens Schipkowski wrote: > On Wed, 06 Dec 2006 13:29:37 +0100, Dave Cramer <pg@fastcrypt.com> > wrote: > >> Unless you specifically ask for it postgresql doesn't lock any >> rows when you update data. >> > Thats not right. UPDATE will force a RowExclusiveLock to rows > matching the WHERE clause, or all if no one is specified. > @Joost Kraaijeveld: Yes, because there is no EntryExclusiveLock or > something like that. Roughly you can say, each UPDATE statement > iterates through the affected table and locks the WHERE clause > matching records (rows) exclusivly to prevent data inconsistancy > during the UPDATE. After that your rows will be updated and the > lock will be repealed. > You can see this during an long lasting UPDATE by querying the > pg_locks with joined pg_stats_activity (statement must be enabled). Apparently I've completely misunderstood MVCC then.... My understanding is that unless you do a select ... for update then update the rows will not be locked . Dave > >> Dave >> On 6-Dec-06, at 2:04 AM, Joost Kraaijeveld wrote: >> >>> Does PostgreSQL lock the entire row in a table if I update only 1 >>> column? >>> >>> >>> --Groeten, >>> >>> Joost Kraaijeveld >>> Askesis B.V. >>> Molukkenstraat 14 >>> 6524NB Nijmegen >>> tel: 024-3888063 / 06-51855277 >>> fax: 024-3608416 >>> web: www.askesis.nl >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 9: In versions below 8.0, the planner will ignore your desire to >>> choose an index scan if your joining column's datatypes do >>> not >>> match >>> >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 9: In versions below 8.0, the planner will ignore your desire to >> choose an index scan if your joining column's datatypes do not >> match > > > > CU, > Jens > > -- > ** > Jens Schipkowski > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
On Wed, Dec 06, 2006 at 08:26:45AM -0500, Dave Cramer wrote: > Apparently I've completely misunderstood MVCC then.... My > understanding is that unless you do a select ... for update then > update the rows will not be locked . The discussion was about updates, not selects. Selects do not in general lock (except for ... for update, as you say). To (partially) answer the original question: The number of columns updated does not matter for the locking situation. /* Steinar */ -- Homepage: http://www.sesse.net/
Hi, Dave Cramer wrote: > Apparently I've completely misunderstood MVCC then.... Probably not. You are both somewhat right. Jens Schipkowski wrote: >> Thats not right. UPDATE will force a RowExclusiveLock to rows >> matching the WHERE clause, or all if no one is specified. That almost right, RowExclusiveLock is a table level lock. An UPDATE acquires that, yes. Additionally there are row-level locks, which is what you're speaking about. An UPDATE gets an exclusive row-level lock on rows it updates. Please note however, that these row-level locks only block concurrent writers, not readers (MVCC lets the readers see the old, unmodified row). > My understanding > is that unless you do a select ... for update then update the rows will > not be locked. Also almost right, depending on what you mean by 'locked'. A plain SELECT acquires an ACCESS SHARE lock on the table, but no row-level locks. Only a SELECT ... FOR UPDATE does row-level locking (shared ones here...) The very fine documentation covers that in [1]. Regards Markus [1]: PostgreSQL Documentation, Explicit Locking: http://www.postgresql.org/docs/8.2/interactive/explicit-locking.html
On Wed, Dec 06, 2006 at 08:26:45AM -0500, Dave Cramer wrote: > >>Unless you specifically ask for it postgresql doesn't lock any > >>rows when you update data. > >> > >Thats not right. UPDATE will force a RowExclusiveLock to rows > >matching the WHERE clause, or all if no one is specified. > > Apparently I've completely misunderstood MVCC then.... My > understanding is that unless you do a select ... for update then > update the rows will not be locked . I think it comes down to what you mean by RowExclusiveLock. In MVCC, writers don't block readers, so even if someone executes an update on a row, readers (SELECT statements) will not be blocked. So it's not a lock as such, more a "I've updated this row, go find the new version if that's appropriate for your snapshot". Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On Dec 5, 2006, at 11:04 PM, Joost Kraaijeveld wrote: > Does PostgreSQL lock the entire row in a table if I update only 1 > column? Know that updating 1 column is actually updating the whole row. So if one transaction updates column A of a row, it will block another concurrent transaction that tries to update column B of the same row. As was mentioned however, neither of these transactions block others reading the row in question, though they see the row as it existed before the updates until those update transactions commit. If you know that your application will suffer excessive update contention trying to update different columns of the same row, you could consider splitting the columns into separate tables. This is an optimization to favor write contention over read performance (since you would likely need to join the tables when selecting) and I wouldn't do it speculatively. I'd only do it if profiling the application demonstrated significantly better performance with two tables. -Casey
Casey Duncan wrote: > On Dec 5, 2006, at 11:04 PM, Joost Kraaijeveld wrote: > >> Does PostgreSQL lock the entire row in a table if I update only 1 >> column? > > Know that updating 1 column is actually updating the whole row. So if > one transaction updates column A of a row, it will block another > concurrent transaction that tries to update column B of the same row. > As was mentioned however, neither of these transactions block others > reading the row in question, though they see the row as it existed > before the updates until those update transactions commit. > > If you know that your application will suffer excessive update > contention trying to update different columns of the same row, you > could consider splitting the columns into separate tables. This is an > optimization to favor write contention over read performance (since > you would likely need to join the tables when selecting) and I > wouldn't do it speculatively. I'd only do it if profiling the > application demonstrated significantly better performance with two > tables. > > -Casey Or, come up with some kind of (pre)caching strategy for your updates wherein you could then combine multiple updates to the same row into one update. -- erik jones <erik@myemma.com> software development emma(r)