Thanks Mike.
I understand the possible "gaps" that I might have if I use the ROLLBACK.
This is acceptable in my case.
What I really want to avoid is what I am doing now: open one transaction to
insert, or update, or delete certain information and close with the commit.
Then, I get the LAST_INSERT_ID() and open another transaction to write my
audit trail. However, if the first one went through ok but if I got a
problem at the second transaction, I need to delete the inserted or updated
or move back the deleted information. This doesn't work well.
Let's expand my "staff" and "change" tables to have this structure to
simulate my problem:
+----+------+--------+
| ID | Name | Gender |
+----+------+--------+
+----+-----+-------+-------+-------+
| ID | Key | Table | Field | Value |
+----+-----+-------+-------+-------+
And do this:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `M`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'ID', LAST_INSERT_ID());
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
I will get something like this:
mysql> select * from staff;
+----+------+--------+
| ID | Name | Gender |
+----+------+--------+
| 1 | ABC | M |
+----+------+--------+
1 row in set (0.00 sec)
mysql> select * from changes;
+----+-----+-------+-------+-------+
| ID | Key | Table | Field | Value |
+----+-----+-------+-------+-------+
| 1 | 1 | staff | ID | 1 |
+----+-----+-------+-------+-------+
| 2 | 1 | staff | Name | ABC |
+----+-----+-------+-------+-------+
| 3 | 2 | staff | Gender| M |
+----+-----+-------+-------+-------+
3 row in set (0.00 sec)
See that I have a problem in the third line at the "Key" column where I
should have "1" but I got "2" instead. This happened because of
LAST_INSERT_ID() used the ID from the "changes" table instead of the desired
"staff" table.
Is there any way to avoid this? What about the mysql_insert_id()?
Andre
On 11/28/06 7:50 PM, "Mike Kruckenberg" <[EMAIL PROTECTED]> wrote:
> Andre Matos wrote:
>> SET AUTOCOMMIT=0;
>> START TRANSACTION;
>> INSERT INTO staff (`Name`) VALUES ('ABC');
>> INSERT INTO changes (`Key`, `Table`, `Value`) VALUES (LAST_INSERT_ID(),
>> 'staff', 'ABC');
>> COMMIT;
>> SET AUTOCOMMIT=1;
>>
>> This works fine in my test environment, however what about many users doing
>> at the same time. Does the LAST_INSERT_ID() get the correct ID for each
>> user?
>>
>> Is there a better way to do this or this is fine? I will be using this with
>> PHP4.
>>
> To further clarify (my initial reply didn't give much detail), when an
> auto increment value is created for inserting it is in the scope of the
> current connection, and is not changed by the outcome of the transaction.
>
> If you follow the string of SQL statements against your tables you'll
> see that the ID assigned to the record is not released on a rollback,
> the second insert gets a new auto increment value.
>
> mysql> SET AUTOCOMMIT=0;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> START TRANSACTION;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> INSERT INTO staff (`Name`) VALUES ('ABC');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
> (LAST_INSERT_ID(),
> -> 'staff', 'ABC');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from staff;
> +----+------+
> | ID | Name |
> +----+------+
> | 1 | ABC |
> +----+------+
> 1 row in set (0.00 sec)
>
> mysql> select * from changes;
> +----+-----+-------+-------+
> | ID | Key | Table | Value |
> +----+-----+-------+-------+
> | 1 | 1 | staff | ABC |
> +----+-----+-------+-------+
> 1 row in set (0.00 sec)
>
> mysql> select last_insert_id();
> +------------------+
> | last_insert_id() |
> +------------------+
> | 1 |
> +------------------+
> 1 row in set (0.00 sec)
>
> mysql> rollback;
> Query OK, 0 rows affected (0.26 sec)
>
> mysql> select * from staff;
> Empty set (0.00 sec)
>
> mysql> select * from changes;
> Empty set (0.00 sec)
>
> mysql> INSERT INTO staff (`Name`) VALUES ('ABC');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
> (LAST_INSERT_ID(),
> -> 'staff', 'ABC');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from staff;
> +----+------+
> | ID | Name |
> +----+------+
> | 2 | ABC |
> +----+------+
> 1 row in set (0.00 sec)
>
> mysql> select * from changes;
> +----+-----+-------+-------+
> | ID | Key | Table | Value |
> +----+-----+-------+-------+
> | 2 | 2 | staff | ABC |
> +----+-----+-------+-------+
> 1 row in set (0.00 sec)
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
