Avoid Duplicate entries MySQL and PHP

Question

I have developed an web ticketing system at my work place and have gone into a strange trouble.

All the tickets raised are stored into a mysql table. The open tickets are identified by a column 'assigned' wherein 'false' is the default value of the tickets not assigned. Then an engineer clicks a button, 'Get Ticket' on the web page and based on priority a ticket is assigned to the engineer.

The tool worked perfect, however, since the past 2-3 days, when the ticket flow increased, there are occurrences that 2 or 3 engineers press the 'Get Ticket' button together and all of them are assigned the same ticket.

Can anyone help me what is the best possible way to avoid such bug.

I have even tried to lock the database, but didn't work. I am looking towards MySQL transactions, but i think that might slow down the application.

Please help with any suggestions!

Please show the structure of the tables and the relations between the tables (also note the logical relations the application enforces). — Mihai Stancu, Mar 26 '13 at 11:01

score 1 · Answer 1 · answered Mar 26 '13 at 11:01

1

You need to use transactions. Transactions in a database environment provide isolation between programs accessing a database concurrently. If this isolation is not provided, the program's outcome are possibly erroneous.

answered Mar 26 '13 at 11:01

johnk

390
1
4
14

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

You should use MySQL transaction in your PHP code

useful link PHP + MySQL transactions examples

edited May 23 '17 at 12:21

Community

1
1

answered Mar 26 '13 at 11:07

Amir

4,089
4
16
28

score 1 · Answer 3 · answered Mar 26 '13 at 11:13

If your database structure contains

a unique identifier for the tickets
the "assigned" flag
an "assigned_to" column for the user id
a ticket creation timestamp and/or a ticket update timestamp

You can fix this problem with an atomical update and a limit clause on the update:

UPDATE  tickets
    SET
        assigned = TRUE,
        updated_at = NOW(),
        assigned_to = @current_user_id
    WHERE assigned = FALSE
    LIMIT 1;

Then you can "reap" the open tickets that are already assigned to your current user id using a select:

SELECT *
    FROM tickets
    WHERE assigned_to = @current_user_id
    ORDER BY updated_at DESC
    LIMIT 1;

This takes advantage of the atomic nature of any update clause. Since your update work does not have an imperative need to be broken down into multiple statements you don't have an imperative need for transactions.

If the updates were more complex requiring you to select data from various places an compose it as an update afterwards you would then of course need transactions.

Thanks Mihai Stancu, this is exactly what i have done, but there are approx 50 users logged in. the only change is after the update statement, there is an insert statement into another database which is used there after. So before the select query is fired, the update and insert query is also fired for another user who clicks the button. any suggestions how to limit this. — Guns, Mar 26 '13 at 11:25
How do you fetch the data for the insert statement? Via select? Do you select the data before or after you update? — Mihai Stancu, Mar 26 '13 at 11:44

Avoid Duplicate entries MySQL and PHP

3 Answers3