locking tables? get back last unique ID after insert?

[SpreadTooThin]

I want to insert a record into a table and then get back the Unique ID for the Last Inserted Row.

CREATE TABLE IF NOT EXISTS `MyDatabase`.`JobID` (
`JobID` INT NOT NULL AUTO_INCREMENT ,
`SubmissionTime` DATETIME NOT NULL ,
PRIMARY KEY (`JobID`) )
ENGINE = InnoDB;

USE `MyDatabase`;
INSERT INTO `JobID` (`JobID`, `SubmissionTime`) Values (NULL, `2013-07-10 11:11:11`);


So how do I get back the JobID that was just added safely?

As Submission TIme may not be unique (it only has 1 second granularity.) I can't get the JobID based on the Submission Time...

If I've understood the documentation then the following should return the JobID I just added.

SELECT LAST_INSERT_ID();

But should I have some table locking around the insert and select?
I've never used table locking so I don't know a lot about it...

[Peter H. Coffin]

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

Short answer: you don't need to lock anything because that
last_insert_id() function is a connection-aware thing that does the
right thing for you.


--
27. I will never build only one of anything important. All important
systems will have redundant control panels and power supplies. For
the same reason I will always carry at least two fully loaded
weapons at all times. --Peter Anspach's Evil Overlord list

[The Natural Philosopher]

On 10/07/13 18:30, Peter H. Coffin wrote:
> On Wed, 10 Jul 2013 10:19:22 -0700 (PDT), SpreadTooThin wrote:
>> I want to insert a record into a table and then get back the Unique ID for the Last Inserted Row.
>>
>> CREATE TABLE IF NOT EXISTS `MyDatabase`.`JobID` (
>> `JobID` INT NOT NULL AUTO_INCREMENT ,
>> `SubmissionTime` DATETIME NOT NULL ,
>> PRIMARY KEY (`JobID`) )
>> ENGINE = InnoDB;
>>
>> USE `MyDatabase`;
>> INSERT INTO `JobID` (`JobID`, `SubmissionTime`) Values (NULL, `2013-07-10 11:11:11`);
>>
>>
>> So how do I get back the JobID that was just added safely?
>>
>> As Submission TIme may not be unique (it only has 1 second granularity.) I can't get the JobID based on the Submission Time...
>>
>> If I've understood the documentation then the following should return the JobID I just added.
>>
>> SELECT LAST_INSERT_ID();
>>
>> But should I have some table locking around the insert and select?
>> I've never used table locking so I don't know a lot about it...
>>
> http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
>
> Short answer: you don't need to lock anything because that
> last_insert_id() function is a connection-aware thing that does the
> right thing for you.
>
>

to amplify that, its stored inside the connection state that you opened.
As long as you dont close it....

--
Ineptocracy

(in-ep-toc’-ra-cy) – a system of government where the least capable to lead are elected by the least capable of producing, and where the members of society least likely to sustain themselves or succeed, are rewarded with goods and services paid for by the confiscated wealth of a diminishing number of producers.

[Axel Schwenke]

"Peter H. Coffin" <hel...@ninehells.com> wrote:
> On Wed, 10 Jul 2013 10:19:22 -0700 (PDT), SpreadTooThin wrote:

>> ... how do I get back the JobID that was just added safely?

>> SELECT LAST_INSERT_ID();

>>
>> should I have some table locking around the insert and select?

> Short answer: you don't need to lock anything because that
> last_insert_id() function is a connection-aware thing that does the
> right thing for you.

In addition the MySQL API used by your application might export the
mysql_insert_id() function. PHPs mysqli does. Perls DBD::mysql too.

The C API function is explained here:

http://dev.mysql.com/doc/refman/5.6/en/mysql-insert-id.html

and all explanations given there apply to other APIs as well.

Using this function will save a network roundtrip (compared to
SELECT LAST_INSERT_ID()). Maybe you don't even need the ID in
your application. I.e. if you want to use it in your next SQL
statement, just put LAST_INSERT_ID() there.


XL

[Thomas 'PointedEars' Lahn]

Axel Schwenke wrote:

> "Peter H. Coffin" <hel...@ninehells.com> wrote:
>> On Wed, 10 Jul 2013 10:19:22 -0700 (PDT), SpreadTooThin wrote:
>>> ... how do I get back the JobID that was just added safely?
>>> SELECT LAST_INSERT_ID();
>>>
>>> should I have some table locking around the insert and select?
>> Short answer: you don't need to lock anything because that
>> last_insert_id() function is a connection-aware thing that does the
>> right thing for you.
>
> In addition the MySQL API used by your application might export the
> mysql_insert_id() function. PHPs mysqli does. Perls DBD::mysql too.

JFTR: The more versatile PDO (PHP Data Objects) API of PHP has
PDO::lastInsertId() also for MySQL. It works fine.

<http://php.net/manual/en/pdo.lastinsertid.php>

--
PointedEars

Twitter: @PointedEars2
Please do not Cc: me. / Bitte keine Kopien per E-Mail.