锁定MySQL中的最大值

Question

I am using the following query on MySQL using PHP

$sql = SELECT MAX(SrNo) FROM cart;
$result = mysql_query($sql);

The structure of table CART is

CART (SrNo int(10));

Now I am using the result to do some kind of processing and inserting the maximum value into this table by incrementing one. My problem is that if user1 has got the maximum value of SrNo and is in-between the processing. During this time user2 also requests the server got the same maximum value of SrNo as user1 got and starts processing.

Now when both are done with the processing + insertion into the table, I will have two duplicates in the table CART. How can I prevent this from happening?

In other words, I want no one else to get the maximum value of SrNo until unless one user is finished doing its processing.

Answer 1

wouldn't you be fine with the AUTO_INCREMENT feature for PRIMARY KEY?

create table cart ( SrNo int(10) AUTO_INCREMENT PRIMARY KEY ) ENGINE = InnoDB;

then just simply insert new lines and it will automatically increment the new values. That would probably very easily do the trick you are (maybe?) trying to do.

But if you need to lock the maxmium, you can do this:

start transaction;
select max(SrNo) from cart for update;
/* do some other stuff, insert the max value + 1 etc... */
commit;

Remember: You should use transaction for any operation which is not 1 single query!