List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:August 15 1999 7:19am
Subject:Re: LAST_INSERT_ID()
View as plain text  
At 01:24, 19990815, toxalot@stripped wrote:
>One more thought.  Should I LOCK TABLES before I INSERT

You don't need to LOCK TABLES if you're just doing the insert.  You
usually want to LOCK TABLES if you need to check some values, and
then INSERT conditionally (i.e., only INSERT if something IS NULL).
You need atomicity - checking the conditions and updating the table
have to happen "at the same time", as far as the outside world (any
threads/clients other than the one doing the INSERT) can tell.

If you're just doing an insert, though, you don't need to lock the
tables - a single insert is guaranteed by MySQL to be atomic.

One common case is wanting to INSERT a row only if it hasn't already
been inserted.  This assumes that you have some way of equating rows,
usually with a primary or unique key.  The obvious way to do this is:

    LOCK TABLES table_name WRITE;

    SELECT id FROM table_name WHERE id = 'new_key_value';

    /* IF the empty set was returned, THEN */

    INSERT INTO table_name (id, foo) VALUES ('new_key_value', 'bar');

    UNLOCK TABLES;

This same thing can be accomplished without locking the tables, by
relying on the unique constraint of the primary key:

    INSERT INTO table_name (id, foo) VALUES ('new_key_value', 'bar');

    /* IF mysql_errno == ER_DUP_ENTRY, THEN row wasn't inserted */

This isn't really about your original question.  But it is about
LOCK TABLES (or not LOCK TABLES).

Tim
Thread
LAST_INSERT_ID()toxalot15 Aug
  • Re: LAST_INSERT_ID()Paul DuBois15 Aug
    • Re: LAST_INSERT_ID()toxalot15 Aug
      • Re: LAST_INSERT_ID()Thimble Smith15 Aug
      • ezmlm-send: fatal...Paul DuBois15 Aug
        • Re: ezmlm-send: fatal...Darrell Shifflett15 Aug
          • Re: ezmlm-send: fatal...Darrell Shifflett15 Aug
        • Re: ezmlm-send: fatal...Martin Ramsch15 Aug
        • Re: ezmlm-send: fatal...Frederik Lindberg16 Aug
  • Re: LAST_INSERT_ID()Martin Ramsch15 Aug