PHP, MySql and concurrency

[wolverine4277]

The scenario is this, i have one table with a filed of type varchar(n) that cannot contain duplicated values except the sting '00000' (then i cannot use one unique index for the field validation).

One example of a valid scenario is:

 

id field

-------------

1 11201

2 31242

3 00000

4 14649

...

124 00000

125 23234

 

now, if i want to insert a tuple (126, 31242) i will not able to do.

The first thing that comes to me mind was checking that the field value not exist and then do the insert:

 

<?php
    $id = mysql_pconnect(..., ..., ...);
    if ($id) {
        if (mysql_select_db(..., $id)) {
            $valid_value = false;
            if ($_POST['value'] != '00000') {
                $valid_value = true;
            } else {
                $sql = "SELECT COUNT(*) AS total\n";
                $sql.= "FROM   table\n";
                $sql.= "WHERE  (field <> '00000') AND (field = '" . $_POST['value'] . "')";
                $dataset = mysql_query($sql, $id);
                if ($dataset) {
                    if ($registro = mysql_fetch_assoc($dataset)) {
                        if ($registro['total'] == 0) {
                            $sql = "INSERT INTO tabla(field)\n";
                            $sql.= "VALUES ('" . $_POST['value']. "')";
                            $result = mysql_query($sql, $id);
                            if (!$result) {
                                // Error message
                            } else {
                                // Ok
                            }
                        } else {
		    // Error message
                        }
                    } else {
                        // Error message
                    }
                } else {
                    // Error message
                }
            } else {
               // Error message
            }
        } else {
            // Error message
        }
    }
?> 

 

but if i don't miss anything, with concurrency of users for insertions that code fail. The code is only for example purposes (not debugged, not enchanced).

 

• User 1 has executing the first SQL sentence and don't finde the value for insert and go on

• User 2 executes the first SQL sentence and then don't find the value for insert and go on (meanwhile the User 1)

• User 2 insert the value

• User 1 insert the value

 

¿Which is the better (or a good one) approximation to do insertions without duplicate values of the field? ¿I must use transactions?

BEGIN TRANSACTION

Execute SQL sentence 1

Execute SQL sentence 2

COMMIT or ROLLBAK transaction

Thanks