List:General Discussion« Previous MessageNext Message »
From:Yves Goergen Date:November 12 2007 10:58pm
Subject:Re: Transactions and locking
View as plain text  
On 12.11.2007 23:31 CE(S)T, Perrin Harkins wrote:
> On Nov 12, 2007 5:24 PM, Yves Goergen <nospam.list@stripped> wrote:
>> Damn, I found out that I need table locking *and* transactions.
> 
> What makes you say that?

BEGIN TRANSACTION
SELECT MAX(id) FROM table
INSERT INTO table (id) VALUES (?)
INSERT INTO othertable (id) VALUES (?)
COMMIT

First I find a new id value, then I do several INSERTs that need to be
atomic, and especially roll back completely if a later one fails.

> That Perl module uses the exact technique I described to you with
> updates and LAST_INSERT_ID().

AUTO_INCREMENT isn't portable. Now I only "support" MySQL and SQLite.
But I also did PostgreSQL (until it failed one of the more complex
queries, maybe it comes back one day) and maybe Oracle or whatever will
be compatible, too, so that I then stand there with my AUTO_INCREMENT
and can't use it.

> Frankly, doing the insert and checking for an error seems like a
> pretty reasonable solution to me, since you only have two databases to
> care about at this point.

I wonder if I can safely use an error code to determine this error
condition and then just retry. Here's an interesting page:

http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
> Error: 1022 SQLSTATE: 23000 (ER_DUP_KEY)
> Message: Can't write; duplicate key in table '%s'

No documentation for SQLite.

PostgreSQL uses several SQLSTATE codes for this situation.
(http://www.postgresql.org/docs/8.2/interactive/errcodes-appendix.html)

Something must be wrong with SQL-92 because the two reference tables
have no common SQLSTATE values for related error conditions. But
generally I think that an SQLSTATE beginning with "23" is close enough
for a match.

-- 
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