Hello everyone.


I have created the following table:


CREATE TABLE public.books (
name VARCHAR PRIMARY KEY
);


And I have started a transaction in 2 different clients (for example, 2
different SQL windows in PgAdmin), so:


START TRANSACTION;


Then, I have noticed that, if I execute:

INSERT INTO public.books VALUES ('Test Book');


on both clients, the second one hangs until I commit or rollback the
other transaction.

So, I wish to know:

1)Why does this happen? Aren't 2 transactions mutually independent?
2)Is there a way to execute the 2 INSERT commands at the same time? And
if not, what could I do to prevent the hanging of the second client?


I'm using PostgreSQL 8.1.4.

Thanks in advance.


Bye
Dynomath

Dynomath <Dynomath (AT) nospam (DOT) com> wrote:
A primary key is the unique identifier of a row in the table, so no two
rows can have the same primary key.

If both transactions succeeded in inserting the row, there would be
an inconsistency.

Usually, if you don't use transactions, the second insert will get an
error message because the insert would violate a uniqueness constraint.

In your case, however, the first insert is not committed at the time the
second transaction tries to insert the same row, so there is no way yet
of knowing whether the second insert will succeed (when the first
transaction rolls back) or fail (when the first transaction commits).

PostgreSQL uses locks to deal with the situation: before inserting the row,
the transaction must obtain a certain lock. If it cannot get that lock
because another transaction holds a conflicting lock (as in your case),
the transaction must wait until the lock goes away.
That happens as soon as the other transaction is completed.

What you observe is correct behaviour.

My question: Why would you want PostgreSQL to behave otherwise?
What is your real problem?

Yours,
Laurenz Albe

Laurenz Albe wrote:
Well, I am developing a software which should manage a book database.

If the user wants to add a book, the program asks the book name, starts
a transaction and adds the book to the "books" table.

I said "starts the transaction" because the process of adding a book is
quite long, and the user must insert a lot of data, and those data will
be used by another program, so every book must have all of its data
inserted: if I didn't start a transaction, and if the user's PC turned
off just after inserting the book in its table, the book would appear to
exist, but its data would be incomplete.
On the contrary, if I start a transaction and two users insert the same
book, the second one's client hangs... and this happens because the
primary key can "see" inside each still uncommitted transaction.

I have worked a lot this afternoon, and maybe I have found a solution:
now, I am using asynchronous notification: doing this, the second client
is automatically stopped after a number of seconds...

Do you think this could be a good solution? Or there is a more elegant
one to solve my problem?


Thank you very much.

Yours
Dynomath

Dynomath <Dynomath (AT) nospam (DOT) com> wrote:
Here's my idea how to do it:

- Do not use the book name as primary key. It may change (e.g., if
somebody has made a typo). How about the ISBN?

- Have the user enter the primary key.

- Check with a select if the book already exists. If yes, error out.

- Have the user enter all the data for the book. Cache them in your
application and check them for sanity (possibly using database queries).

- As soon as the user is done, start a transaction and add the book
to the database. Then commit.

Drawback: in the unlikely event that somebody else has made a conflicting
entry in the meantime, the transaction will fail AFTER the user has gone
through the tedious process of entering all the data.

Advantage: You don't have to hold locks for a long time, which is always
a bad idea.

Try to avoid transactions that take a long time, especially ones that
include user interaction - what if the user walks away or dies while
entering the book? How do you detect that and cancel the transaction?

Yours,
Laurenz Albe

Laurenz Albe wrote:
Thank you a lot for your very, very valuable advice.
I'll try to do as you told me.
I'm quite new to DB servers, so I still have some problems when working
with transactions, locks, and so on...


In my situation, this won't happen! ^______^ It will be used by less
than 10 people... Anyway, thank you for the advice.


Thank you very much.

Yours
Dynomath

Begin <eOO%g.17013$uv5.126967 (AT) twister1 (DOT) libero.it>
On 2006-10-25, Dynomath <Dynomath (AT) nospam (DOT) com> wrote:
The database is supposed to take care about the details of making
transactions and so on work. You do need to know the basics, though.
The database will keep its tables consistent (free of half-entered
records that can cause havoc) but you have to tell it what parts belong
together, and you want to transfer the data close together as much as
possible.

I'd probably consider doing a query for the ISBN as soon as it is
entered and at least notify the user that such an ISBN already exists.
Being responsive to the user is useful, but don't let the application
become bogged down and slow because of the many queries it does. Once
the entry is completed and submitted by the user, do locking, full
checking, and committing to the database.


Don't say ``won't happen'', because it will. I know from experience that
even one person other than you can already trigger all sorts of ``can't
happen'' situations. The less experienced and more ``user'' a user is
the more likely s/he is to trigger that sort of thing. You need to spend
some thought on how you will be solving that problem already, even if
you feel you don't want to actually solve it right now. Plan for it
anyway.


--
j p d (at) d s b (dot) t u d e l f t (dot) n l .
This message was originally posted on Usenet in plain text.
Any other representation, additions, or changes do not have my
consent and may be a violation of international copyright law.

jpd wrote:
Yes, of course. I'm studying them a lot.

I'll keep this advice in mind.

Thank you very, very much for your valuable advice. I'll try to do as
you told me.


Bye
Dynomath