From: Vitalii Tymchyshyn <tivv00@gmail.com>
To: alan <alan.miller3@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: insert
Date: Mon, 01 Aug 2011 11:52:17 +0300
Message-ID: <4E366941.9070507@gmail.com> (raw)
In-Reply-To: <a3899672-2ff6-4174-bc31-07f5c4c2a113@l37g2000yqd.googlegroups.com>
References: <2e208bc6-1bb8-4170-809f-77ab63a15bf4@l18g2000yql.googlegroups.com>
<a3899672-2ff6-4174-bc31-07f5c4c2a113@l37g2000yqd.googlegroups.com>
Hello.
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.
Best regards, Vitalii Tymchyshyn
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tivv00@gmail.com, alan.miller3@gmail.com
Subject: Re: insert
In-Reply-To: <4E366941.9070507@gmail.com>
* 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