MySQL Forums
Forum List  »  PHP

Re: PHP and Transactions
Posted by: Peter Black
Date: January 16, 2006 09:18AM

a) I will look into it

b) I set autocommit to false before starting the transaction.

SET AUTOCOMMIT=0;
BEGIN;

c) Basically I am creating a user registration script. First I am using a select to see if the email address the user has entered is already in the DB for an existing account. If the resulting resource is false or the email address is already in use I rollback and throw an exeption.

Next I check a temporary table for the same thing. I use a temporary table to insert the user until they can confirm their email. That is where the delete comes into play. I give the user 24 hours to confirm, so if the email address is returned, I throw an exception and rollback, UNLESS the expiration date has expired, whereupon I delete the row and continue. And I also make sure the resulting resource from mysql_qeury is not false, otherwise I throw an exception. I do this for all queries. So that is how the insert can still happen.

Next, I do the same thing as above, only I am checking to see if the username they entered exists, instead of email.

Finally, I insert the user into the temp table, to await confirmation.

So, when I run my test case, I am sending the same username and email with 100 threads, so only one thread should ever reach the insert statement since I have the check for the email, so once it inserts that email address, the other threads should never make it to the insert.

In conclusion, it seems that other threads are coming in between the select statements and the insert statement.

Options: ReplyQuote


Subject
Written By
Posted
January 14, 2006 03:42PM
January 15, 2006 05:51AM
Re: PHP and Transactions
January 16, 2006 09:18AM
January 19, 2006 12:00AM
January 19, 2006 02:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.