Vitalii Tymchyshyn <tivv00@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
