Re: insert - Mailing list pgsql-performance

Vitalii Tymchyshyn <tivv00@gmail.com> wrote:

> Please note that in multitasking environment you may have problems
> with your code. Two connections may check if "a" is available and
> if not (and both got empty "select" result), try to insert. One
> will succeed, another will fail if you have a unique constraint on
> category name (and you'd better have one).
>
> Please note that select for update won't help you much, since this
> is new record you are looking for, and select don't return (and
> lock) it. I am using "lock table <tableName> in SHARE ROW
> EXCLUSIVE mode" in this case.
>
> But then, if you have multiple lookup dictinaries, you need to
> ensure strict order of locking or you will be getting deadlocks.
> As for me, I did create a special application-side class to
> retrieve such values. If I can't find a value in main connection
> with simple select, I open new connection, perform table lock,
> check if value is in there. If it is not, add the value and
> commit. This may produce orphaned dictionary entries (if
> dictionary entry is committed an main transaction is rolled back),
> but this is usually OK for dictionaries. At the same time I don't
> introduce hard locks into main transaction and don't have to worry
> about deadlocks.

It sounds like you might want to check out the new "truly
serializable" transactions in version 9.1.  If you can download the
latest beta version of it and test with
default_transaction_isolation = 'serializable' I would be interested
to hear your results.  Note that you can't have deadlocks, but you
can have other types of serialization failures, so your software
needs to be prepared to start a transaction over from the beginning
when the SQLSTATE of a failure is '40001'.

The Wiki page which was used to document and organize the work is:

http://wiki.postgresql.org/wiki/Serializable

This is in a little bit of a funny state because not all of the
wording that was appropriate while the feature was under development
(e.g., future tense verbs) has been changed to something more
appropriate for a finished feature, but it should cover the
theoretical ground pretty well.  An overlapping document which was
initially based on parts of the Wiki page and has received more
recent attention is the README-SSI file here:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/backend/storage/lmgr/README-SSI;hb=master

Some examples geared toward programmers and DBAs is at this Wiki
page:

http://wiki.postgresql.org/wiki/SSI

It could use a couple more examples and a bit of language cleanup,
but what is there is fairly sound.  The largest omission is that we
need to show more explicitly that serialization failures can occur
at times other than COMMIT.  (I got a little carried away trying to
show that there was no blocking and that the "first committer
wins".)

-Kevin