How MySQL manage multiple queries from multiple users simultaneously?

Question

Just to give you an example:

I have a PHP script that manages users votes.

When a user votes, the script makes a query to check if someone has already voted for the same ID/product. If nobody has voted, then it makes another query and insert the ID into a general ID votes table and another one to insert the data into a per user ID votes table. And this kind of behavior is repeated in other kind of scripts.

The question is, if two different users votes simultaneously its possible that the two instances of the code try to insert a new ID (or some similar type of query) that will give an error??

If yes, how I prevent this from happening?

Thanks?

Important note: I'm using MyISAM! My web hosting don't allow InnoDB.

Answer 1

This when the singleton pattern come in handy. It ensure that a code is executed only by one process at an instant.

http://en.wikipedia.org/wiki/Singleton_pattern

You have to make a singleton class for the database access this will prevent you from the type of error you describing.

Cheers.

Answer 2

The question is, if two different users votes simultaneously its possible that the two instances of the code try to insert a new ID (or some similar type of query) that will give an error??

Yes, in general this is possible. This is an example of a very common problem in concurrent systems, called a race condition.

Avoiding it can be rather tricky, but in general you need to make sure that the operations cannot interleave in the way you describe, e.g. by locking the database for a while.

There are several practical solutions to this, all with their own advantages and risks (e.g. dead locks). See the Wikipedia article for a discussion and further pointers to information.

Answer 3

The question is, if two different users votes simultaneously its possible that the two instances of the code try to insert a new ID (or some similar type of query) that will give an erro

Yes, you might end up with two queries doing the insert. Depending on the constraints on the table, one of them will either generate an error, or you'll end up with two rows in your database.

You could solve this, I believe, with applying some locking; e.g. if you need to add a vote to the product with id theProductId:(pseudo code)

START TRANSACTION;
//lock on the row for our product id (assumes the product really exists)
select 1 from products where id=theProductId for update;
//assume the vote exist, and increment the no.of votes
update votes set numberOfVotes = numberOfVotes + 1 where productId=theProductId ;
//if the last update didn't affect any rows, the row didn't exist
if(rowsAffected == 0) 
  insert into votes(numberOfVotes,productId) values(1,theProductId )
//insert the new vote in the per user votes
insert into user_votes(productId,userId) values(theProductId,theUserId);
COMMIT;

Some more info here

MySQL offers another solution as well, that might be applicable here, insert on duplicate

e.g. you might be able to just do:

 insert into votes(numberOfVotes,productId) values(1,theProductId ) on duplicate key 
   update numberOfVotes = numberOfVotes  + 1;

If your votes table have a unique key on the product id column, the above will do an insert if the particular theProductId doesn't exist, otherwise it will do an update, where it increments the numberOfVotes column by 1

You could probably avoid a lot of this if you created a row in the votes table at the same time you added the product to the database. That way you could be sure there's always a row for your product, and just issue an UPDATE on that row.

Answer 4

The easiest way:

LOCK TABLES table1 WRITE, table2 WRITE, table3 WRITE 
-- check for record, insert if not exists, etc...
UNLOCK TABLES

If voting does not occur many times per second, then the above should be sufficient.

InnoDB tables offer transactions, which might be useful here as well. Others have already commented on it, so I won't go into any detail.

Alternatively, you could solve it at the code level via using some sort of shared memory mutex that disables concurrent execution of that section of PHP code.