Re: Checking the status of a field, and ensuring it is not updated before I change it
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] >
Re: Checking the status of a field, and ensuring it is not updated before I change it
Joshua Beall wrote: 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 UPDATE proctable SET status='running' WHERE procid=23 AND status='stopped'; If this fails someone else already started it. OR UPDATE proctable SET status='running', starter='myid' WHERE procid=23 AND status='stopped'; SELECT status,starter FROM proctable WHERE procid=23; Is it running, and do you own it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Checking the status of a field, and ensuring it is not updated before I change it
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]
