Thread: Strange Behavior with Serializable Transcations
I'm seeing something fairly unintuitive about serializable transactions. Taking the following test case: CREATE TABLE foo (id integer); t1 t2 -- BEGIN; -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; INSERT INTO foo (id) -- VALUES (1); -- -- SELECT * from foo; The select in t2 sees the row inserted from t1, which it shouldn't. 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." Now, if I modify the case as such: t1 t2 -- BEGIN; -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- SELECT * from foo; INSERT INTO foo (id) -- VALUES (1); -- -- SELECT * from foo; The select in t2 (the last one, obviously) does not see the insert from t1. What's up? -- Brad Nicholson 416-673-4106 bnichols@ca.afilias.info Database Administrator, Afilias Canada Corp.
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: <snip> > 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 <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On Wed, Jun 28, 2006 at 14:48:01 -0400, Brad Nicholson <bnichols@ca.afilias.info> 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
Martijn van Oosterhout <kleptog@svana.org> 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
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: > > <snip> > > > 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
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 <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
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
"Tom Lane" <tgl@sss.pgh.pa.us> 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 ------- 9999 (1 row) test=# lock table k; LOCK TABLE Regards, Qingqing
"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
> "Tom Lane" <tgl@sss.pgh.pa.us> 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
"Tom Lane" <tgl@sss.pgh.pa.us> 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
On Thu, Jun 29, 2006 at 14:27:30 +0200, Martijn van Oosterhout <kleptog@svana.org> 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.