Question :
What I want to do is insert a row and than select its auto incremented id number, In a multhi-threaded enviroment.
I know how to do it with a Stored Procedure.
Will this line of code work:
BEGIN TRANSACTION;
INSERT INTO table (r1) VALUES (...);
SELECT id FROM table ORDER BY id DESC LIMIT 1;
COMMIT;
And are there any other options?
Answer :
No. Instead:
INSERT ...
SELECT LAST_INSERT_ID();
LAST_INSERT_ID() is session-specific, so there is never any confusion with other threads also inserting into the same table.
Also, there is no need for BEGIN … COMMIT for this pair of statements. (You may need it if there are other statements.)
I have practiced that as follows:
BEGIN TRANSACTION;
INSERT INTO table (r1) VALUES (...);
SELECT id FROM table ORDER BY id DESC LIMIT 1;
COMMIT;
It gives the following result:
BEGIN
INSERT 0 1
id
----
4
(1 row)
COMMIT