Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

How can I improve the following code with row locking / stored procedure.

UPDATE `data` SET status=':temp_status' WHERE account_id = :account_id AND dataset_id=':dataset' AND status=':source' LIMIT $limit;

SELECT id,phone,firstname,lastname FROM `data` WHERE account_id = :account_id AND status = ':temp_status';

UPDATE `data` SET status=':destination',status_updated=NOW() WHERE account_id = :account_id AND status = ':temp_status';

I am essentially reading data from a table my many clients to then further process, I need to make sure that each client only grabs a bunch of unique records, which then can't be obtained by any other users.

I am running this from PHP with multi-query on InnoDB tables;

Can I achieve this with row locking?

share|improve this question

closed as off topic by Zoredache, voretaq7 Nov 11 '12 at 3:17

Questions on Server Fault are expected to relate to professional server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Seems to me that this belongs on stackoverflow, or possibly dba. –  Zoredache Jul 31 '12 at 19:08
add comment

1 Answer

Row locking is the default locking feature of InnoDB

You may need to do two things:

First, setup a transaction

BEGIN TRANSACTION;
UPDATE `data` SET status=':temp_status' WHERE account_id = :account_id AND dataset_id=':dataset' AND status=':source' LIMIT $limit;
SELECT id,phone,firstname,lastname FROM `data` WHERE account_id = :account_id AND status = ':temp_status';
UPDATE `data` SET status=':destination',status_updated=NOW() WHERE account_id = :account_id AND status = ':temp_status';
COMMIT;

Second, (this is optional) experiment with switching the Transaction Isolation Level during the DB Session. This may provide the needed intermittency of data you need.

share|improve this answer
    
Hi, thanks for this, but just wrapping this inside a transaction isnt really going to improve efficiency. I am really looking for a better way I can re-write the code to achieve what I need to do. –  Jonathan Aug 1 '12 at 11:47
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.