SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-19-2008, 08:50 AM
| ||||
| ||||
 Re: Locking in PostgreSQL? 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings      |
|
04-19-2008, 08:50 AM
| |||
| |||
| Re: [GENERAL] Locking in PostgreSQL? 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/i...t-locking.html ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate |
|
04-19-2008, 08:50 AM
| |||
| |||
| Re: [GENERAL] Locking in PostgreSQL? 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. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFFdsl3IB7bNG8LQkwRAnwWAKCDKqlHPYIVPoo5n8LcIR 1jXewWjwCfbu/8 Wt5bWPvcWOy16oESM/dGgTY= =53TX -----END PGP SIGNATURE----- |
|
04-19-2008, 08:50 AM
| |||
| |||
| Re: Locking in PostgreSQL? 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 > ---------------------------(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 |
|
04-19-2008, 08:50 AM
| ||||
| ||||
| Re: Locking in PostgreSQL? 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/ ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings |
Linear Mode
