If your table is MyISAM you will have to lock the table so that no other process can access that row (or any other) while you do your check and update.
http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html If your table is InnoDb you can still do the table lock or you can lock just the one row. http://dev.mysql.com/doc/mysql/en/InnoDB_locking_reads.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine news <[EMAIL PROTECTED]> wrote on 11/11/2004 07:38:20 AM: > Hi All, > > I have the following situation: I want to check a row in a database (list of > jobs that need to be run). So, my script checks the status field, and if it > is not started, then it marks it as locked and running, and when it > finishes, it marks it as completed. > > Now, here is what I am concerned about - because there may be many processes > running at any one time, it seems feasible to me that two instance of my > script might simultaneously issue the SELECT statement, and seeing that the > job is not started, then subsequently issue UPDATE statements. Both > processes "think" they are the only ones running. > > How should I deal with this? It seems to me that I need some sort of query > that, all in one transaction, first locks the row so that no other processes > can access it, then checks to see if it is running/completed or not. If it > is not yet running/completed, it starts the job, updates the database row to > mark the job as running, and then unlocks the row. > > Is this what I should do? I'm not sure how to do this. Any thoughts? Any > help would be greatly appreciated! > > Sincerely, > -Josh > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
