At 18:37 -0800 3/19/02, Allon Bendavid wrote:
>Thanks Paul for all of the clarifications
>Actually what I was saying was this:
>I obviously didn't assume that sticking a semi-colon between statements
>creates a transaction. I was leaving out the transaction/table lock
>statements but I meant to illustrate that for instance:
>LOCK TABLES Orders WRITE;
> SELECT @a:=MAX(Number) FROM Orders;
> INTO Orders (...)
> VALUES (..., @a+1);
> UNLOCK TABLES;
>Traditionally this is executed in one statement.
I'm not sure what that is supposed to mean.
>So your translation is:
> Statement s = conn.createStatement ();
> s.execute ("LOCK TABLES Orders WRITE");
> s.executeQuery ("SELECT @a:=MAX(Number) FROM Orders");
> s.executeUpdate ("INSERT INTO Orders (...) VALUES (..., @a+1);");
> s.execute ("UNLOCK TABLES");
> s.close ();
> catch (SQLException e)
>Would work as expected? (Ignoring the obvious invalidity of my Insert
It should. I'm not sure that you need to lock the tables, though, if
you're using them within a transaction. That in itself should ensure the
atomicity of your operation.
I'm not certain why you don't just use an AUTO_INCREMENT column for this,
though. If the reason is that you already have an AUTO_INCREMENT column
elsewhere in the table (and thus cannot create another one), a different
approache you could try is to use another table that maintains the
order number sequence.
CREATE TABLE fake_sequence (i INT UNSIGNED NOT NULL);
INSERT INTO fake_sequence SET i = 0;
That creates the table and initializes the counter. To generate the
next sequence, do this:
UPDATE fake_sequence SET i = LAST_INSERT_ID(i+1);
Then to get the value of the value you just generated, do this:
This has the important property of AUTO_INCREMENT values that the SELECT
will return the value *you* just generated, even if other clients generate
sequence number in between your UPDATE and your SELECT.
I guess you could to this, even:
UPDATE fake_sequence SET i = @a := LAST_INSERT_ID(i+1);
Then @a would have the value you want.
>The lock would occur on the statements and the insert would get the value of
>Thanks a lot for your help this should be a winner.