Evert | Rooftop wrote:
Reading all this I suddenly realise I have the same problem. I'm using a modified tree traversal alghoritm to store my tree in the database, but when I insert a new node I assign new ID's based on a SELECT query. I think I can deal with it with the combined UPDATE..SELECT query. Right now an anomaly only occured once and I had a recovery script running to try to fix the situation (it worked!). But there's a good chance this was the cause.
That's kind of what I meant in my initial reply with the comment about transactions. If you need the lock on the table rows to extend over several SQL statements, and you couldn't fix this by using, for example, an UPDATE..SELECT query, your best bet would be to wrap the statements that *had* to be done together in a transaction [1].
Then you would write your SELECT statements as SELECT .. FOR UPDATE which locks the rows selected until the end of the current transaction [2]. So your SQL looks like: START TRANSACTION SELECT ... FOR UPDATE [...] UPDATE ... COMMIT [1] http://dev.mysql.com/doc/mysql/en/transactional-commands.html [2] http://dev.mysql.com/doc/mysql/en/select.html -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ If you find my advice useful, please consider donating to a poor student! You can choose whatever amount you think my advice was worth to you. http://tinyurl.com/7oa5s -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
