On 10/30/06, Stuart Clarke <[EMAIL PROTECTED]> wrote: > > On Mon, 2006-10-30 at 22:26 -0300, Jorge Godoy wrote: > > Stuart Clarke <[EMAIL PROTECTED]> writes: > > > > > I have a script which accesses the DB, and which can be started in the > > > background from my TG web application (The script also uses my app's > > > model). I want to prevent this script from being simultaneously > > > executed more than once. Problem is, the web app may be replicated > > > across a number of load-balanced servers. So, what mechanism can I use > > > to prevent simultaneous execution. The only things shared by my > > > replicated apps would be the DB server, and memcached. > > > > Use a table with a flag. Check that before starting and if it's there, > > abort > > the execution. If it's not, then start your operation by setting the flag > > so > > that no other instance of the script can be run. > > *** The test-and-set operation isn't atomic though, is it? Which still > leaves the door (slightly) open for simultaneous executions. > > Might be the best possible solution, however.
It depends on how your DB works and what your workers are supposed to do. For example: [A] BEGIN; [B] BEGIN; [A] SELECT locked FROM locktable; (false) [A] UPDATE locktable SET locked='t'; [B] SELECT locked FROM locktable; (false) [B] UPDATE locktable SET locked='t'; (BLOCKED) [A] -- Do some work [A] UPDATE locktable SET locked='f'; [A] COMMIT; [B] -- locked is set to 't' because B is no longer blocked [B] -- Do some work [B] COMMIT; The work ends up done twice, because [B] can not know that the lock was ever acquired. It does see a consistent view of the database though, because it had to wait for the row lock to be released by [A] before it has a chance to do its update. Which view of the database it sees depends on the isolation level though. If you want [B] to fail you need to use two transactions. One to acquire the lock, one to do the work. You also need to use a locking select or they'll *both* succeed in updating the lock table and bad things will happen. [A] BEGIN; [B] BEGIN; [A] SELECT locked FROM locktable FOR UPDATE; (false) [B] SELECT locked FROM locktable FOR UPDATE; (BLOCKED) [A] UPDATE locktable SET locked='t'; [A] COMMIT; [B] -- SELECT returns 't' so we can bail here [B] COMMIT; [A] BEGIN; [A] -- Do work [A] UPDATE locktable SET locked='f'; [A] COMMIT; Using something other than a boolean in there can make sense so you can track which process performed the lock and when, so you can force-release the lock eventually. [A] might not have completed the transaction. -bob --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "TurboGears" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/turbogears -~----------~----~----~----~------~----~------~--~---
