[drupal-support] How to use MySQL last_insert_id function with locking?
Hi, I am developing my own module which uses tables that have auto-increment fields. As far as I can tell, drupal uses a single connection for my default configuration. I don't think i think use last_insert_id without locking because the MySQL DB connection is shared accross requests. so what should I lock in order to get the correct result from last_insert_id? Please give me an example. Thanks. Kai
KZ Mai wrote:
Hi, I am developing my own module which uses tables that have auto-increment fields.
As far as I can tell, drupal uses a single connection for my default configuration.
I don't think i think use last_insert_id without locking because the MySQL DB connection is shared accross requests.
so what should I lock in order to get the correct result from last_insert_id?
I'm not positive I understand what you are asking, but Drupal's use of a single database connection does not preclude you from using last_insert_id(). Each thread or process of your web server will have its own copy of the database connection used by Drupal, and MySQL will properly keep track of the last ID used in an auto_increment field for each connection. Even if the thread running your code is interrupted by the operating system between the insert and your call to last_insert_id(), no other thread will use that connection and that information in last_insert_id() will remain valid when your thread is resumed. -- Chris Johnson
KZ Mai wrote:
Hi, I am developing my own module which uses tables that have auto-increment fields.
As far as I can tell, drupal uses a single connection for my default configuration.
I don't think i think use last_insert_id without locking because the MySQL DB connection is shared accross requests.
so what should I lock in order to get the correct result from last_insert_id?
Well I'm not sure it's a good idea using last_insert_id, because such function won't work if users of your module use pgSQL so, I think you should try to keep compatibility between DB. For this Drupal use db_next_id function that is preferable to mysql auto_incrment fields. So using this you get the next ID for your table and then you can make the INSERT. Nicolas
I was under the impression that when drupal uses a single shared MySQL connection, using last_insert_id to get the newly created rows in tables may cause problems. Say in my drupal module, User A tries to create a new row in a table, before my module calls last_insert_id. User B creates a new row in the same table. Is it possible that urser A gets back the id which is associated with the row User B created? Yes. I know last_insert_id works for MySQL only. I don't have much time to refactor my codes to use db_next_id. so I just want to get the easiest solution. Thanks. Kai On 7/21/05, Chris Johnson <chris@tinpixel.com> wrote:
KZ Mai wrote:
Hi, I am developing my own module which uses tables that have auto-increment fields.
As far as I can tell, drupal uses a single connection for my default configuration.
I don't think i think use last_insert_id without locking because the MySQL DB connection is shared accross requests.
so what should I lock in order to get the correct result from last_insert_id?
I'm not positive I understand what you are asking, but Drupal's use of a single database connection does not preclude you from using last_insert_id(). Each thread or process of your web server will have its own copy of the database connection used by Drupal, and MySQL will properly keep track of the last ID used in an auto_increment field for each connection. Even if the thread running your code is interrupted by the operating system between the insert and your call to last_insert_id(), no other thread will use that connection and that information in last_insert_id() will remain valid when your thread is resumed.
-- Chris Johnson
-- [ Drupal support list | http://lists.drupal.org/ ]
KZ Mai wrote:
I was under the impression that when drupal uses a single shared MySQL connection, using last_insert_id to get the newly created rows in tables may cause problems.
Say in my drupal module, User A tries to create a new row in a table, before my module calls last_insert_id. User B creates a new row in the same table.
Is it possible that urser A gets back the id which is associated with the row User B created?
No. To repeat what I wrote previously in another way: yes, Drupal may use only one connection, but that is one connection PER INSTANCE of the web server (e.g. child processes if you are using Apache on a Unix system). Each instance of the web server will finish the Drupal PHP code it is executing for one HTTP request before it services another. The only way User B can even create a row before User A gets the last ID back is to use a DIFFERENT MySQL connection.
participants (3)
-
Chris Johnson -
KZ Mai -
Nicolas Tostin