pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Paesold <mpaesold@gmx.at>
To: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
Cc: pgsql-admin@postgresql.org
Subject: Re: duplicates
Date: Mon, 6 Sep 2004 12:40:01 +0200
Message-ID: <00a701c493fd$dd44d060$ad01a8c0@zaphod> (raw)
References: <Pine.LNX.4.58.0409060922390.20253@p-roman.jct.ac.il>
	<008601c493e5$e0965940$0b00a8c0@forge>
	<Pine.LNX.4.58.0409061050190.20694@p-roman.jct.ac.il>
	<00c701c493eb$def5b9e0$0b00a8c0@forge>
	<Pine.LNX.4.58.0409061211580.20694@p-roman.jct.ac.il>
	<009501c493f6$52932770$ad01a8c0@zaphod>
	<Pine.LNX.4.58.0409061252510.20694@p-roman.jct.ac.il>

Tsirkin Evgeny wrote:

> On Mon, 6 Sep 2004, Michael Paesold wrote:
> Does not the Serializable Isolation Level do insure that?
> what i thought is that while using this level then i am getting
> the BEGIN and COMMIT to behave the same as the code you wrote!
> since the second concarent transaction (the one that started a little
> later) should wait for the first one and only then DELETE .
> Is not that true?

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

DELETE FROM schedule WHERE studentid = ... ;
INSERT INTO schedule (studentid, ...) VALUES (... );
INSERT INTO schedule (studentid, ...) VALUES (... );

COMMIT;

If you do it like in the above sql code, there is still a problem. The
serializable checking here only works, if DELETE FROM schedule... really
finds at least one row. If it does not, it will not recognize the
serialization problem. So it's still possible that your programm creates
duplicates.

I have tested this here. I don't really know if this is just the case with
PostgreSQL serializable transactions (MVCC limitation) or a general problem.


> PS Does not the perl::DBI use the serializable level?

I don't really know about DBI, you should really check that.
* check that autocommit is off
* check that transaction isolation level is serializable


Still serializable transaction level is not enough. An aproach that allows
more concurrency is possible, but you have to make sure, that all relevant
code does the locking in the same way:
(assuming you have a student table with studentid as primary/unique key)

BEGIN;

SELECT * FROM student WHERE studentid = ... FOR UPDATE;
   -- lock the student record

DELETE FROM schedule WHERE studentid = ... ;
INSERT INTO schedule (studentid, ...) VALUES (... );
INSERT INTO schedule (studentid, ...) VALUES (... );

COMMIT;

This will not lock the whole schedule table, but only one row of the student
table.

I hope that helps.

Best Regards,
Michael Paesold




view thread (14+ messages)  latest in thread

Message-ID: <00a701c493fd$dd44d060$ad01a8c0@zaphod>
Permalink:  ../00a701c493fd$dd44d060$ad01a8c0@zaphod/
Also on:    postgresql.org/message-id/00a701c493fd$dd44d060$ad01a8c0@zaphod

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: mpaesold@gmx.at, tsurkin@mail.jct.ac.il
  Subject: Re: duplicates
  In-Reply-To: <00a701c493fd$dd44d060$ad01a8c0@zaphod>
  References: <Pine.LNX.4.58.0409060922390.20253@p-roman.jct.ac.il> <008601c493e5$e0965940$0b00a8c0@forge> <Pine.LNX.4.58.0409061050190.20694@p-roman.jct.ac.il> <00c701c493eb$def5b9e0$0b00a8c0@forge> <Pine.LNX.4.58.0409061211580.20694@p-roman.jct.ac.il> <009501c493f6$52932770$ad01a8c0@zaphod> <Pine.LNX.4.58.0409061252510.20694@p-roman.jct.ac.il> <00a701c493fd$dd44d060$ad01a8c0@zaphod>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox