Avatar of CarlosScheidecker
 asked on

How to garantee that different client threads work on different subsets of data


I have a table of 1.000 Urls. I also have 4 clients that need to work on those URLs at the same time. I would like to garantee that each client works on unique URLs. Meaning, I want to divide that by four so that each one has 250 URLs to work with. Those URLs change. What I would like to do is to do some sort of locking on a subset of URLs so that only that client can read those 250 URLs with a select * form tbl_URL while the others can only do the same within their set.

I need to make sure that no 2 clients process the same URLs.

Once the URL is processed, it can be relased back to the pool.

How can I do that?

MySQL ServerDatabases

Avatar of undefined
Last Comment

8/22/2022 - Mon

What I thought so far was:

1) Pass two values to the client: client number and total number of clients.
2) Perform a count and divide from the total number of clients.
3) Once I have the size of one chunk, then I can do a limit with start index and number of total rows to fetch,

4) The start index will be number of client - 1, times the chunk. If client is client one, than 0 times chunk  equal to index 0. If client is 2, then 1 times chuk size gives the start index.

Since URLs not only change on their size but can be edited, I cannot do those SQL statically.

Any other ideas?

In case you do this from a free programmable application I´d insert a 'lock' flag into the table setting the userid as lock or '0' as default (better performance on indeices rather than NULL).
Combine that with your ideas and you should be good to give it a go.

I do not want to lock the table or a set of tuples. I think this will be a problem while writing to it.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

This is not about DB level table locks.
All you do is to expand the table for a column like 'work_lock'. All you do is to fire up an SQL to insert the userid into 250 rows, column work_lock. Do the insert into tables that do not have a work_lock set.
All users will be able to work with data from this table this way. As soon as work on a row has been done, reset the column back to NULL or whatever and everything is fine.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

This is the answer to that question. Tried and worked fine.