Exclusive access to MySQL database in PHP
I was just thinking(yehr right...) the other day. On my webpage I have a discussionforum. The database keeps track of the total number of posts and the number of headline-posts("toplevel threads"). And when I add a new post I use these numbers for identification of the posts.
This poses the following problem:
When I retrieve the numbers from the database and when I add the post to the database there could be the possibility that another php-thread(ie. another user) does the same thing. And then the numbers will change before the new post is inserted into the database and the numbers will be wrong.
Should I do anything to prevent this myself, or does MySQL do some sort of mutual exclusion itself?
My thoughts on solving the problem are:
1. Make some sort of semaphore-based exclusive right access system.
2. Write the whole databse command in one line to MySQL and hope that it only make one request at a time.
Am I shooting birds with cannons here?(can you even say that in english?? :)
And whats the best solution(read: the most easy to implement!)?