List:General Discussion« Previous MessageNext Message »
From:Yves Goergen Date:November 14 2007 11:21pm
Subject:Re: Transactions and locking
View as plain text  
On 14.11.2007 12:50 CE(S)T, Martijn Tonies wrote:
> Yves,
> 
> Did you read this reply I send earlier? I think it does what you
> want without needing to "lock" anything, thus making it portable.

>> I would suggest the following --
>>
>> create a table called "SEQUENCES":

Yes, I've read it and actually put a flag on that message, but then I
decided to go for the other flagged message that explained SELECT ...
FOR UPDATE. I did some tests with multiple client windows and found that
the locking is good enough. I use it for finding new ID values and for
telling whether a new value is unique where UNIQUE constraints won't
help me (because I want the values to be caseless unique e.g.).

SELECT ... FOR UPDATE works fine when I always use the same function to
access that table. It is supported by MySQL, PostgreSQL and Oracle. I
only need a small workaround for SQLite (which gets the "FOR UPDATE"
stripped off and instead requires the programmer to have started an
EXCLUSIVE transaction before; else -> Exception).

Sequences, if I got that right, need the new value to be stored
immediately, i.e. outside of an active transaction. This requires a
second connection to the database which probably causes more
implementation work for my web application.

I don't need LOCK TABLES anymore now. And at last, I can say that this
is indeed not a simple topic as I've thought and maybe I've read most of
the related documentation now anyway...

-- 
Yves Goergen "LonelyPixel" <nospam.list@stripped>
Visit my web laboratory at http://beta.unclassified.de
Thread
Transactions and lockingYves Goergen12 Nov
  • Re: Transactions and lockingMartijn Tonies12 Nov
  • Re: Transactions and lockingPerrin Harkins12 Nov
    • Re: Transactions and lockingYves Goergen12 Nov
      • Re: Transactions and lockingPerrin Harkins12 Nov
      • Re: Transactions and lockingYves Goergen12 Nov
        • Re: Transactions and lockingYves Goergen12 Nov
          • Re: Transactions and lockingPerrin Harkins12 Nov
            • Re: Transactions and lockingYves Goergen12 Nov
              • Re: Transactions and lockingPerrin Harkins13 Nov
                • Re: Transactions and lockingYves Goergen13 Nov
                  • mysqlhotcopyMalka Cymbalista13 Nov
                  • Re: Transactions and lockingBaron Schwartz13 Nov
                    • Re: Transactions and lockingmark addison13 Nov
                      • Re: Transactions and lockingYves Goergen13 Nov
                        • Re: Transactions and lockingBaron Schwartz13 Nov
                          • Re: Transactions and lockingYves Goergen13 Nov
                            • Re: Transactions and lockingBaron Schwartz13 Nov
                          • Re: Transactions and lockingPerrin Harkins13 Nov
                            • Re: Transactions and lockingYves Goergen13 Nov
                              • Re: Transactions and lockingBaron Schwartz13 Nov
                                • Re: Transactions and lockingYves Goergen13 Nov
                    • Re: Transactions and lockingYves Goergen13 Nov
                    • Re: Transactions and lockingYves Goergen13 Nov
                      • Re: Transactions and lockingBaron Schwartz13 Nov
                        • Re: Transactions and lockingYves Goergen13 Nov
                        • Re: Transactions and lockingYves Goergen13 Nov
                          • Re: Transactions and lockingPerrin Harkins13 Nov
  • Re: Transactions and lockingMartijn Tonies13 Nov
  • Re: Transactions and lockingMartijn Tonies14 Nov
    • Re: Transactions and lockingYves Goergen15 Nov
  • Re: Transactions and lockingMartijn Tonies15 Nov
Re: Transactions and lockingYves Goergen13 Nov
  • Re: Transactions and lockingPerrin Harkins13 Nov