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;
> INSERT
> 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:
>
>try
> {
> 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
>statement).
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:
SELECT LAST_INSERT_ID();
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
>the select?
>
>Thanks a lot for your help this should be a winner.
>
>-Allon
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
