CODEWITHSUNDEEP
mysqllockingrowinnodbmyisam
tom smith
tom smith

Reputation: 1035

MySQL row locking myisam innodb

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.

Upvotes: 1

Views: 1493

Answers (4)

Y.K.
Y.K.

Reputation: 310

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.

Upvotes: 0

Brent Baisley
Brent Baisley

Reputation: 12721

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.

Upvotes: 0

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44333

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

Upvotes: 1

Joshua Dickerson
Joshua Dickerson

Reputation: 402

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.

Upvotes: 0

Related Questions