According to the mySQL docs the database uses the current maximum value in the column + 1 as the new value for an insert. I don't want this behavior since I use the key which is currently the AUTO_INCREMENT field as part of the URLs query string.
For example, the user bookmarks the URL which contains the key, and later the row is removed. Then if the user attempts to view the bookmark or attempts to search for the item via the key then I want the site to say that the item does not exist anymore.
With the way mySQL AUTO_INCREMENT works, if the last row is deleted and then someone else decides to insert an item thus reusing the erased key, then when the previous user decides to view the bookmarked URL or search the site for the key, then another item would come up. This would probably confuse the user.
So, it seems that I can't use the AUTO_INCREMENT attribute on the field, and need an alternative method to generate monotonically increasing keys. Basically, it would work with how MS Access generate keys. I thought about saving the last inserted key value into a file and then increasing the key value when an update was performed, but then worried about concurrency issues.
What would happen if two or more users opened the file at the same time and attempted to write the same value, or if two or more users try to update the file at the same time (e.g. user 1 reads key #8000, user 2 reads key #8000 also, user 2 writes #8001, user 1 then writes #8001 also).
I'm new to PHP and so I guess what I'm looking for is some sort mutex or critical section locking mechanism either in PHP itself or via the OS.
Thanks in advance for any help!
For example, the user bookmarks the URL which contains the key, and later the row is removed. Then if the user attempts to view the bookmark or attempts to search for the item via the key then I want the site to say that the item does not exist anymore.
With the way mySQL AUTO_INCREMENT works, if the last row is deleted and then someone else decides to insert an item thus reusing the erased key, then when the previous user decides to view the bookmarked URL or search the site for the key, then another item would come up. This would probably confuse the user.
So, it seems that I can't use the AUTO_INCREMENT attribute on the field, and need an alternative method to generate monotonically increasing keys. Basically, it would work with how MS Access generate keys. I thought about saving the last inserted key value into a file and then increasing the key value when an update was performed, but then worried about concurrency issues.
What would happen if two or more users opened the file at the same time and attempted to write the same value, or if two or more users try to update the file at the same time (e.g. user 1 reads key #8000, user 2 reads key #8000 also, user 2 writes #8001, user 1 then writes #8001 also).
I'm new to PHP and so I guess what I'm looking for is some sort mutex or critical section locking mechanism either in PHP itself or via the OS.
Thanks in advance for any help!