Re: [GENERAL] Strange Behavior with Serializable Transcations
On Thu, Jun 29, 2006 at 14:27:30 +0200, Martijn van Oosterhout wrote: > On Thu, Jun 29, 2006 at 01:21:19PM +0100, Simon Riggs wrote: > > The issue is the difference between start of transaction and time when > > the serializable snapshot is taken. Since BEGIN and other commands may > > be issued as separate network requests it makes sense to defer taking > > the snapshot until the first time it is needed. The transaction is still > > serializable, just that the manual is worded slightly incorrectly with > > regards the exact timing. > > I've always interpreted it as "there exists a serialised order for the > transactions" but the database makes no guarentees about what it might > be. I can't think of any real world case where you actually care about > the order, just as long as one exists. Postgres' serializable mode doesn't guaranty that. To get that effect you may need to do some extra locking. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Strange Behavior with Serializable Transcations
"Tom Lane" <[EMAIL PROTECTED]> wrote > > It can, but there are cases where you want the lock to be taken before > the snapshot is set. Otherwise, there could be committed changes in the > database that you can't see in your snapshot. I think there are some > examples in the manual, or check the archives. > Ok, here are the links: http://archives.postgresql.org/pgsql-bugs/2002-12/msg00169.php - a bug analysis of the take-snapshot at wrong point; http://archives.postgresql.org/pgsql-hackers/2004-09/msg00133.php - recent redesign notes. Regards, Qingqing ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Strange Behavior with Serializable Transcations
"Qingqing Zhou" <[EMAIL PROTECTED]> writes: > "Tom Lane" <[EMAIL PROTECTED]> wrote >> Right, the snapshot does not become set until you do a non-utility >> command (normally, SELECT/INSERT/UPDATE/DELETE). This is a feature, not >> a bug, because it lets the transaction take table locks before its >> snapshot becomes set. > Hm, mostly I understand it as an optimization. What do you mean by "it lets > the transaction take table locks before its snapshot becomes set"? If we > take a snapshot at BEGIN, then the transaction can't take table locks? It can, but there are cases where you want the lock to be taken before the snapshot is set. Otherwise, there could be committed changes in the database that you can't see in your snapshot. I think there are some examples in the manual, or check the archives. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Strange Behavior with Serializable Transcations
"Tom Lane" <[EMAIL PROTECTED]> wrote > > Right, the snapshot does not become set until you do a non-utility > command (normally, SELECT/INSERT/UPDATE/DELETE). This is a feature, not > a bug, because it lets the transaction take table locks before its > snapshot becomes set. > Hm, mostly I understand it as an optimization. What do you mean by "it lets the transaction take table locks before its snapshot becomes set"? If we take a snapshot at BEGIN, then the transaction can't take table locks? test=# begin;select count(*) from k; BEGIN count --- (1 row) test=# lock table k; LOCK TABLE Regards, Qingqing ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Strange Behavior with Serializable Transcations
On Thu, 2006-06-29 at 14:27 +0200, Martijn van Oosterhout wrote: > On Thu, Jun 29, 2006 at 01:21:19PM +0100, Simon Riggs wrote: > > The issue is the difference between start of transaction and time when > > the serializable snapshot is taken. Since BEGIN and other commands may > > be issued as separate network requests it makes sense to defer taking > > the snapshot until the first time it is needed. The transaction is still > > serializable, just that the manual is worded slightly incorrectly with > > regards the exact timing. > > I've always interpreted it as "there exists a serialised order for the > transactions" but the database makes no guarentees about what it might > be. I can't think of any real world case where you actually care about > the order, just as long as one exists. Agreed, the ordering is irrelevant. Deferring the snapshot provides you with the most accurate *and* consistent view of the database. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Strange Behavior with Serializable Transcations
On Thu, Jun 29, 2006 at 01:21:19PM +0100, Simon Riggs wrote: > The issue is the difference between start of transaction and time when > the serializable snapshot is taken. Since BEGIN and other commands may > be issued as separate network requests it makes sense to defer taking > the snapshot until the first time it is needed. The transaction is still > serializable, just that the manual is worded slightly incorrectly with > regards the exact timing. I've always interpreted it as "there exists a serialised order for the transactions" but the database makes no guarentees about what it might be. I can't think of any real world case where you actually care about the order, just as long as one exists. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Strange Behavior with Serializable Transcations
On Wed, 2006-06-28 at 21:20 +0200, Martijn van Oosterhout wrote: > On Wed, Jun 28, 2006 at 02:48:01PM -0400, Brad Nicholson wrote: > > I'm seeing something fairly unintuitive about serializable transactions. > > > > Taking the following test case: > > > > > http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html > > > > "When a transaction is on the serializable level, a SELECT query sees > > only data committed before the transaction began; it never sees either > > uncommitted data or changes committed during transaction execution by > > concurrent transactions." > > I think the issue here is that transaction begin is not when you type > "begin" but at your first actual query. The issue is the difference between start of transaction and time when the serializable snapshot is taken. Since BEGIN and other commands may be issued as separate network requests it makes sense to defer taking the snapshot until the first time it is needed. The transaction is still serializable, just that the manual is worded slightly incorrectly with regards the exact timing. > You can obviously only start a > transaction once you know what serialisation level you want, and you > don't see that till after the begin. Isolation level can be set on the BEGIN statement, so sometimes you do know, but as mentioned above that's no reason to take the snapshot at that point. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Strange Behavior with Serializable Transcations
Martijn van Oosterhout writes: > I think the issue here is that transaction begin is not when you type > "begin" but at your first actual query. You can obviously only start a > transaction once you know what serialisation level you want, and you > don't see that till after the begin. Right, the snapshot does not become set until you do a non-utility command (normally, SELECT/INSERT/UPDATE/DELETE). This is a feature, not a bug, because it lets the transaction take table locks before its snapshot becomes set. I believe the Fine Manual's discussion of concurrency includes some examples where such locks are needed ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Strange Behavior with Serializable Transcations
On Wed, Jun 28, 2006 at 14:48:01 -0400, Brad Nicholson <[EMAIL PROTECTED]> wrote: > I'm seeing something fairly unintuitive about serializable transactions. > > "When a transaction is on the serializable level, a SELECT query sees > only data committed before the transaction began; it never sees either > uncommitted data or changes committed during transaction execution by > concurrent transactions." This has come up on the lists before. Take a look at: http://secure.linuxports.com/pgsql-admin/2006-01/msg00256.php ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Strange Behavior with Serializable Transcations
On Wed, Jun 28, 2006 at 02:48:01PM -0400, Brad Nicholson wrote: > I'm seeing something fairly unintuitive about serializable transactions. > > Taking the following test case: > http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html > > "When a transaction is on the serializable level, a SELECT query sees > only data committed before the transaction began; it never sees either > uncommitted data or changes committed during transaction execution by > concurrent transactions." I think the issue here is that transaction begin is not when you type "begin" but at your first actual query. You can obviously only start a transaction once you know what serialisation level you want, and you don't see that till after the begin. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature