RE: transaction safety - Mailing list pgsql-general

Hi,

Well, the number is 'locked', because once it's given to you, that's it, it's gone from the 'list of available numbers' (i.e.: the sequence).  However, between the insert, and the read of the ID, if another transaction performs an insert, it does NOT affect the ID that the first transaction reads (i.e.: your ID read in the first transaction IS definitely still safe, it will still read the correct one).  AND, the first insert does NOT block the second insert.  The second insert could complete and commit before the first one.

Does this explain better?

MikeA

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

On Mon, Feb 12, 2001 at 10:22:30AM -0500, Tom Lane wrote:
> DaVinci <bombadil@wanadoo.es> writes:
> > On Mon, Feb 12, 2001 at 01:08:01PM -0000, Michael Ansley wrote:
> >> 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 ;-)
>
> >  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 :)
>
> It's not true.  How did you arrive at that conclusion from what Mike
> said?

 I'll try to explain. Mike said: "in a transaction make an insert and then a
 read in serial current value".
 
 If in gap between those operations occurs another insert from different
 transaction, then reading serial is not safe.

 In order to understand this well I have made some basic experiments,
 freezing a transaction with an insert and making other transaction with an
 insert to the same table. Second gets frozen until first commit or cancel.
 That is reason of my last message: "locks with inserts are for table and
 not for tuple". Perhaps I didn't explain myself very well or there is some
 detail about locks that I don't understand at all.

 Thanks all for your time.

                                                                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
**********************************************************************