Do I need to lock a MySQL table when doing a SELECT followed by an INSERT?

Question

I'm no database guru, so I'm curious if a table lock is necessary in the following circumstance:

• We have a web app that lets users add entries to the database via an HTML form
• Each entry a user adds must have a unique URL
• The URL should be generated on the fly, by pulling the most recent ID from the database, adding one, and appending it to the newly created entry
• The app is running on ExpressionEngine (I only mention this in case it makes my situation easier to understand for those familiar with the EE platform)

Relevant DB Columns (exp_channel_titles)

• entry_id (primary key, auto_increment)
• url_title (must be unique)

My Hypothetical Solution -- is table locking required here?

Let's say there are 100 entries in the table, and each entry in the table has a url_title like entry_1, entry_2, entry_3, etc., all the way to entry_100. Each time a user adds an entry, my script would do something like this:

1. Query (SELECT) the table to determine the last entry_id and assign it to the variable $last_id
2. Add 1 to the returned value, and assign the sum to the variable $new_id
3. INSERT the new entry, setting the url_title field of the latest entry to entry_$new_id (the 101st entry in the table would thus have a url_title of entry_101)

Since my database knowledge is limited, I don't know if I need to worry about locking here. What if a thousand people try to add entries to the database within a 10 second period? Does MySQL automatically handle this, or do I need to lock the table while each new entry is added, to ensure each entry has the correct id?

Running on the MyISAM engine, if that makes a difference.

Answer 1

I think you should look at one of two approaches:

• Use and AUTO_INCREMENT column to assign the id
• Switching from MyISAM to the InnoDb storage engine which is fully transactional and wrapping your queries in a transaction