Ok, I'm working on a PHP/MySQL site, and I'm trying to figure out some of the theory behind database driven sites like this.
Basically, I'm interested in how a site can maintain a database that "makes sense" with multiple scripts modifying the database.
Let's say that you have one script, call it "addbookmark.php", and another, "deleteuser.php". One user calls "addbookmark.php?user=fred&link=blah"; the addbookmark script starts running. It verifies that the user fred exists, and starts doing something else before it inserts the bookmark into the database.
In this time between the user-exists verification and the actual INSERT query, some other user executes "deleteuser.php?user=fred", which deletes the user fred and all bookmarks for fred.
The addbookmark script finishes what it was doing--verifying the link, etc.--and inserts the bookmark into a table. The user for which the bookmark exists, though, was just deleted, although the addbookmark script can't know this; you now have a "ghost" bookmark.
How do you avoid situations like this? I would think that true foreign keys would help, but MySQL doesn't have foreign key support (does it?). In addition to true foreign keys, how else can you keep a database sane? Are the chances of a situation like this actually occurring just too low to worry about?
-Bryan
Basically, I'm interested in how a site can maintain a database that "makes sense" with multiple scripts modifying the database.
Let's say that you have one script, call it "addbookmark.php", and another, "deleteuser.php". One user calls "addbookmark.php?user=fred&link=blah"; the addbookmark script starts running. It verifies that the user fred exists, and starts doing something else before it inserts the bookmark into the database.
In this time between the user-exists verification and the actual INSERT query, some other user executes "deleteuser.php?user=fred", which deletes the user fred and all bookmarks for fred.
The addbookmark script finishes what it was doing--verifying the link, etc.--and inserts the bookmark into a table. The user for which the bookmark exists, though, was just deleted, although the addbookmark script can't know this; you now have a "ghost" bookmark.
How do you avoid situations like this? I would think that true foreign keys would help, but MySQL doesn't have foreign key support (does it?). In addition to true foreign keys, how else can you keep a database sane? Are the chances of a situation like this actually occurring just too low to worry about?
-Bryan