CODEWITHSUNDEEP
mysqlmyisam
mpen
mpen

Reputation: 282995

Atomic queries in MySQL/MyISAM

Suppose I want to select a maximum value from a table using MySQL w/ MyISAM engine and then insert a new record with this maximum + 1 based on this value (after applying a transformation to it). I don't want any other transactions happening at the same time that will read the same maximum value and then try to insert the same new record.

How do I prevent this from happening?

Do I need to LOCK the entire table, or what?

To be clear, I'm fairly certain I can't use AUTO_INCREMENT here unless I can redefine the operation it performs to something considerably more complex than "plus one".

Upvotes: 3

Views: 653

Answers (2)

Matt S
Matt S

Reputation: 15374

LOCK TABLES `tbl` WRITE;
INSERT INTO `tbl` (`id`, `foo`) SELECT MAX(`id`)+1, 'bar' FROM `tbl`;
UNLOCK TABLES;

But you should use AUTO_INCREMENT if it's a standard primary key / ID type field.

Upvotes: 3

Joe Stefanelli
Joe Stefanelli

Reputation: 135838

Let the database do the work for you so you don't have to worry about this. Use the auto_increment feature that's already built in.

Upvotes: 1

Related Questions