[Zuminez]

I've been having problems with data corruption in my PHP scripts when dealing with a MySQL database.
The primary key is incremented with every new record, but I do this myself instead of letting MySQL do it for me.

Pseudocode...

Get the last primary key generated...
Get the next new unique primary key by incrementing the last key created...
...
//gap between getting the key and inserting it.
...
Insert new record with new unique primary key into table...

Apparently, a web server's CPU multitasks when more than one person requests a web page.
The corruption is a result of two instances of the above PHP script running at the same time.
Before the first task inserts the new primary key into the table, the second task come through and generates the same key.

To fix this problem, I closed the gap between getting and inserting the new key, but I think that is more of a band-aid than a cure. The gap still exists but is smaller.

I need to know how to fix this. Any help would be appreciated.

[modi123_1]

Quote

The primary key is incremented with every new record, but I do this myself instead of letting MySQL do it for me.

Why are you doing the incrementing and not MYSQL?

[Zuminez]

I didn't know as much about MySQL databases as I do now, but why fix what isn't broken. Would the problem not exist if I let MySQL increment it instead of doing it myself?

Pseudocode...

Insert new record letting MySQL increment the primary key for me.
...
//Gap between inserting the key and getting it.
...
Get The last created key, so we can work with that specific record.

[modi123_1]

Quote

I didn't know as much about MySQL databases as I do now, but why fix what isn't broken.

Clearly something's broken, right?

Quote

Would the problem not exist if I let MySQL increment it instead of doing it myself?

This is correct.

[JackOfAllTrades]

modi123_1, on 27 March 2012 - 05:43 PM, said:

Quote

I didn't know as much about MySQL databases as I do now, but why fix what isn't broken.

Clearly something's broken, right?

Quote

Would the problem not exist if I let MySQL increment it instead of doing it myself?

This is correct.

LOL

[Zuminez]

Thank you, modi123_1.
You were blunt and to the point which was exactly what I needed to look at my code in a new light.


Sometimes seemingly obvious solutions aren't always obvious.
I hope you weren't laughing 'at me', JackOfAllTrades?

[modi123_1]

Nope - he was lacking at my verbosity.. or lack there of.

[Zuminez]

Alright, that takes care of the gap dealing with inserting a new record, but what about with updating a record?

Pseudocode...

user 2(admin) views a webpage showing the information for user 1.
user 1(client) views a webpage showing his own information.

user 1(client) updates his record.
user 2(admin), who is still looking at the original record which is no longer up to date, makes a change and hits the save button.

Am I worrying too much about something that doesn't matter?
What would be an uncomplicated fix for something like this?