1

I've got a theoretical question and can't find a good solution for this on the net:

For a tblA with 100,000 recs.

I want to have multiple processes/apps running, each of which accesses tblA.

I don't want the apps to access the same recs. ie, I want appA to access the 1st 50 rows, with appB accessing the next 50, and appC accessing the next 50 after that..

So basically I want the apps to do a kind of fetch on the next "N" recs in the table. I'm looking for a way to access/process the row data as fast as possible, essentially running the apps in a simultaneous manner. but I don't want the apps to process the same rows.

So, just how should this kind of process be set up?

Is it simply doing a kind of:

 select from tblA limit 50 
 and doing some kind of row locking for each row (which requires innodb)

Pointers/psuedo code would be useful.

Wes
  • 4,781
  • 7
  • 44
  • 53
tom smith
  • 1,035
  • 7
  • 23
  • 39
  • 1
    1.) myisam does table locking, there's no row or page locking. Must use innoDB. http://stackoverflow.com/questions/917640/any-way-to-select-without-causing-locking-in-mysql 2.) get size of full query and then handle sections for each query in your app; mysql will take care of locking – Alfabravo Feb 24 '12 at 19:27
  • As @Alfabravo said, currently only innodb engine is able to do row-level locking.Maybe you should consider such kind of stored procedures processing.However, we don't have much details about what kind of processing you want to do, which is most important for making decision for solution – rkosegi Feb 24 '12 at 19:32

4 Answers4

1

Here is some posts from the DBA StackExchange on this

It discusses SELECT ... LOCK IN SHARE MODE and potential headcahes that comes with it.

Percona wrote a nice article on this along with SELECT ... FOR UPDATE

Community
  • 1
  • 1
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
0

The problem with these solutions is lag time. If process A executes at 12:00:00 and proccess B also executes at precisely the same time, and in an application, there are several blocks of distinct code leading up to the locks/DMLs, the process time for each would vary. So process A may complete first, or it may be process B. If process A is setting the lock, and process B modifies the record first, you're in trouble. This is the trouble with forking.

Y.K.
  • 290
  • 2
  • 9
0

Your application should handle what data it wants to access. Create a pointer in that. If you're using stored procedures, use another table to store the pointers. Each process would "reserve" a set of rows before beginning processing. Every process should check for the max of that and also see if it is greater than the length of the table.

Joshua Dickerson
  • 401
  • 3
  • 11
0

If you are specifically looking for processing first set, second set, etc. The you can use LIMIT # (i.e. 0,50 51,100 101,150) with an ORDER BY. Locking is not necessary since the processes won't even try to access each others record sets. But I can't imagine a scenario where that would be a good implementation.

An alternative is to just to use update with a limit, then select the records that were updated. You can use the process ID, random number or something else that is almost guaranteed to be unique across processes. Add a "status" field to your table indicating if the record is available for processing (i.e. value is NULL). Then each process would update the status field to "own" the record for processing.

UPDATE tblA SET status=1234567890 WHERE status IS NULL LIMIT 50;
SELECT * FROM tblA WHERE status=1234567890;

This would work for MyISAM or Innodb. With Innodb you would be able to have multiple updates running at once, improving performance.

Brent Baisley
  • 12,641
  • 2
  • 26
  • 39