List:Replication« Previous MessageNext Message »
From:Justin Edwards Date:November 9 2010 6:25pm
Subject:Re: Table Locking
View as plain text  
It sounds like you need to program in the intelligence for it to behave
correctly. It shouldn't have anything to do with table locking.  Why not
create a field on the table that says Processing, and assign a true or false
value, have your program check this before starting work on it, and if it is
false, assign it to true?

I personally wouldn't do the processing from both servers unless it was so
slow that it required two servers, although at that point it's probably good
to slice up the filesystem.



Justin Edwards
TeleLanguage Inc.
Network Administrator



On Tue, Nov 9, 2010 at 12:19 PM, Tears ! <unix.co@stripped> 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
> >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: http://lists.mysql.com/replication
> > To unsubscribe:
> > http://lists.mysql.com/replication?unsub=1
> >
> >
>
>
> --
> Umar Draz
> Network Administrator
>

Thread
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