(newbie to MySQL) I've been banging my head against the wall on this one for a bit now, and I understand that last_insert_id() is per-connection based, but most webapps are connection pooled (simple) or clustered (harder). What are my options to get the id of the inserted row in a webapp? As a side note, I'm using JDBC to access the DB.
my thoughts: 1- use an innoDB table, start a txn (lock the table), insert, select max(id_column), end txn (unlock the table) 2- make an id pool table (innodb), have app server grab pool of ids at startup, and when pool is empty in similar manner (lock, update, select, unlock) 3- look to other product (don't make me do this one!!!! ;) ) 4- continue to bang head against the wall please cc me on any replies, as although I sent a subscription request, I'm not on list yet... previous info------------- In the last episode (Sep 19), Steven Kreuzer said: > >What is the SQL to get the created AutoInc ID from a row that I have > >just inserted? > > SELECT MAX(id_field) FROM table Nope. If someone else inserted a record between the time you inserted yours and the time you run that select, your answer will be wrong. Use LAST_INSERT_ID(), or whatever construct your language provides for retrieving it without doing another query. -- Dan Nelson [EMAIL PROTECTED] -------------------------------------- Daniel Greene Manager, Software Development Chelsea Interactive [EMAIL PROTECTED] (571)203-4105 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
