RE: transaction safety

There should be no locks on the table for insert.  The sequence number is retained by the session, not locked on the database.  What this means is that somebody else may start their insert after you, and then you roll back, leaving a gap in the sequences.  However, gaps in the sequences are not (or should not be) important.  You can test this out using a few sessions of psql.

You need to view this with about 110 chars across, in fixed-width font (tab-width of 4 chars):

a$ psql test                                            |       b$ psql test
a=> begin;      b=> begin;                                      |      
a=> insert into person (name, dob)                      |      
a-> values ('Peter', '03/09/1945');                     |      
INSERT XXXX                                                     |      
a=> select currval('person_id_seq');            |      
1                                                               |      
                                                                |       b=> insert into person (name, dob)
                                                                |       b-> values ('Marge', '05/03/1967');
                                                                |       INSERT XXXX
                                                                |       b=> select currval('person_id_seq');
                                                                |       2
a=> select currval('person_id_seq');            |      
1                                                               |      
a=> insert into address (id_person, address)    |      
a-> values (currval('person_id_seq'), 'XXXX');  |      
INSERT XXXY                                                     |      
                                                                |       b=> insert into address (id_person, address)
                                                                |       b-> values (currval('person_id_seq'), 'XXXX');
                                                                |       INSERT XXXY
                                                                |       b=> select currval('person_id_seq');
                                                                |       2
a=> rollback;                                           |      
ROLLBACK                                                        |      
                                                                |       b=> select currval('person_id_seq');
                                                                |       2
                                                                |       b=> end;
                                                                |       COMMIT
                                                                v
                                                            time

Now, although the left side has rolled back, the sequence number 1 is lost.  However, this is not a problem, as the primary key requires uniqueness, but not continuity, and it means that your inserts do not block the table.  Inserts can happen as fast as you can push data into the table.

Read up on sequences, because serial fields are based on sequences, thus avoiding the locking issue.

Hope this helps...

MikeA

-----Original Message-----
From: DaVinci [mailto:bombadil@wanadoo.es]
Sent: 12 February 2001 13:43
To: Lista PostgreSql
Subject: Re: [GENERAL] transaction safety

On Mon, Feb 12, 2001 at 01:08:01PM -0000, Michael Ansley wrote:
 
> The number returned by the sequence for the serial ID is retained within the
> session, and so it can be returned by calling currval, e.g.:

...[a detailed example]...

> Typically, the insert for a person, and for all the associated addresses
> would be done in the same transaction so that if the insert for one of the
> addresses failed, then the whole lot would role back (perhaps a bit extreme,
> but I think that's what you asked for ;-)

 That sounds good ;)

 I thought it is possible to have different transactions opened and insert
 data in same table from them. It seems my idea was fault, doesn't it?.

 In sumary: locks with inserts are for table and not for tuple. If this is
 not true, tell me details, please :)

 Thanks for all.

                                                                                                                                David

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************