Best Practice for Database Concurrency?

[eluser]cyang[/eluser]
Hey everyone. Just getting started with CodeIgniter, and gotta say I'm having a blast. The term that comes to mind is "controlled chaos", as I can still do whatever I want but have some semblance of order ;-)

Anyways, probably a basic question, but is there some best practice for database-related concurrency? Specifically, I'm implementing an user auth system (actually just using Redux Auth) and want to add a uniqueness check on usernames and emails. Since (hopefully) there might be many registrations at once, I'd imagine a simple database scan followed by insert is a definite race condition. I've thought about using the database class transactions (e.g. $this->db->trans_start()) and also adding a uniqueness constraint directly in the database, but I feel like these will make my app platform dependent (e.g. what if, using MySQL, I want to convert my users table to MyISAM for speed, which I believe doesn't support transactions). Also, I'd then have to rely on catching a database error/exception, and I'd rather validate first, rather than try-and-correct (also not 100% sure how PHP/CI does error handling; I remember it's php4, so no exceptions, right?).

So to sum it up, is there some best-practice for this kind of stuff? Thanks, any help would be greatly appreciated!

[eluser]Popcorn[/eluser]
Hi cyang,

Redux has built in functions to check to see if a username or email is in use:

check_username($username);
check_email($email);

You can use them in a custom validation callback.

http://ellislab.com/codeigniter/user-gui...ation.html

[eluser]cyang[/eluser]
Hey Popcorn, thanks for your reply, much appreciated.

Regarding those functions (check_username/email), I looked at their implementation in the library (redux_auth.php), and looks like they ultimately do an SQL SELECT to see if there are any rows with the input username/email.

However, and please let me know if my interpretation is incorrect, I don't think this would solve the concurrency issue. Say you have clients A and B, and they both want to register using username "test". They both call check_username('test') before doing an insert. Though unlikely, if both A and B are trying to register at the same time, the order of execution could be:

Client A's call for check_username('test') resolves, which returns FALSE (i.e. no current 'test' username)
Client B's call for check_username('test') resolves, also returns FALSE
Client A calls register(), inserts a user w/username 'test'
Client B, not knowing Client A had inserted 'test' after his check, also calls register() to insert 'test'

Is there something built into Redux or CodeIgniter to address this? In the past, I've been doing table locks/transactions, but was wondering if there's any way to do it platform independently. Maybe implementing some sort of a lock in SQL? I think for now, I'll just run a non-threadsafe check that will display an error message about a username being taken, and also add a UNIQUE column constraint in SQL and allow the error to bubble up in that one-in-a-million-chance a race condition occurs. Hopefully the user will just try to register again ;-)

[eluser]Sumon[/eluser]
I am not 1% sure this url helps or not. but you may have a look

http://www.itu.dk/courses/INP/F2002/MySQL-intro/p13.php

[eluser]muttlogic[/eluser]
Cyang,

I think your best bet is to add a UNIQUE KEY index to your database table that includes these fields. While you may still want to check for existing records using a SELECT in a validation callback, the INSERT done after validation will ultimately tell you if you've been able to insert unique values into the table. You'll want to test for MySQL errNo 1062 after you've executed the INSERT, which indicates a duplicate was detected and the INSERT failed.

Regards,
Jeff

[eluser]cyang[/eluser]
Hi everyone, thanks for all you suggestions. I think I'm gonna go somewhere along the lines of muttlogic's idea of doing a comparison that might have a race condition, and relying on a UNIQUE constraint in the database to detect it. Thanks again; community's an unexpected but definite plus to working w/CodeIgniter!

[eluser]tobefound[/eluser]
Is it not good practise to ALWAYS scrutinize db action results? For instance, the redux_auth inserts simply are one liners without checking the db return value? As somebody mentioned, it's not odd to add a UNIQUE constraint to a field in the database. Would be nice if there was some other means to monitor say 1062 without having to hack this great lib.

Popcorn?

/T