List:Replication« Previous MessageNext Message »
From:Mats Kindahl Date:November 9 2010 8:36pm
Subject:Re: Table Locking
View as plain text  
Hi Umar,

Since MySQL replication is asynchronous, you always have a risk of
having both WEB1 and WEB2 starting to work on the same user. It is
probably better to "assign" the users to each server (e.g., by having
users with odd user ID processed by WEB1 and even user ID processed by

It is possible to develop some external client to ensure that the row
exists on both servers before proceeding, but the scheme above is
magnitudes simpler.

Just my few cents,
Mats Kindahl

On 11/09/2010 07:19 PM, Tears ! wrote:
> Dear Mats!
> Thanks for your response, Here is what we want,
> We need the lock to insert records from our multiple web servers that
> will synchronize some scheduled tasks. Here is a scenario:
> There are two web servers WEB1 and WEB2. Both have same scheduled jobs
> which will process some files on a network storage server. The files
> belong to users so we can classify the files by users. Let's suppose
> WEB1 started the scheduled job to process files. It will first check
> if the files for User 1 are already being processed if not then insert
> a record in db to prevent WEB2 to process the same user's data. So now
> when WEB2 will start the same scheduled job it will not touch User1's
> files rather move on to the next available user. Here is the algo:
> 1 - Check if a lock is already acquired on User1's files (a row in db
> represents a lock)
> 2 - do some processing and reserve some relevant resources
> 3 - If no lock exists then acquire a lock for User1's files by
> inserting a row in db
> 4 - If lock already has been acquired then move on to the next user
> and start from step 1.
> The problem here is that during step 1 and step 3 we do not want other
> servers to query for any existing locks to avoid duplicate locks.
> Since our two db servers are running in a master-master configuration
> on a shared IP with heatbeat so it might happen that web1 locks a db
> table on db1 and web2 ends up querying db2. That's why we want a table
> to be locked on both db servers.
> Regards,
> Umar
> On Mon, Nov 8, 2010 at 12:47 PM, Mats Kindahl <mats.kindahl@stripped
> <mailto:mats.kindahl@stripped>> wrote:
>     On 11/08/2010 07:13 AM, Tears ! wrote:
>     > Dear All,
>     >
>     > I am running MySQL with Master/Master replication. Yesterday I
>     had been lock
>     > a table on Server B. But on Server A the table was not locked.
>     >
>     > Is it possible when we lock table, it should be locked on both ends.
>     >
>     In general, no.
>     Also, I wonder why you want to lock the other table?
>     Best wishes,
>     Mats Kindahl
>     > Regards,
>     >
>     > Umar
>     >
>     >
>     --
>     MySQL Replication Mailing List
>     For list archives:
>     To unsubscribe:  
> -- 
> Umar Draz
> Network Administrator

Table LockingTears !8 Nov
  • Re: Table LockingMats Kindahl8 Nov
    • Re: Table LockingJohan De Meersman8 Nov
      • Re: Table LockingMats Kindahl8 Nov
        • Re: Table LockingJohan De Meersman9 Nov
    • Re: Table LockingTears !9 Nov
      • Re: Table LockingJustin Edwards9 Nov
        • Re: Table LockingRick James9 Nov
      • Re: Table LockingMats Kindahl9 Nov
      • Re: Table LockingJohan De Meersman10 Nov
        • Re: Table LockingMarcus Bointon10 Nov