Locking a Table in a Stored Procedure

I have a table called 'capital' in mysql.  I am subtracting 10 from a field called 'pendingCapitalAvailable'.  To accomplish this, I am considering using a called procedure that I will call from Matlab.  I need to make sure the table is locked until I finish the procedure.  How can I lock the table while it is updating? I tried 'LOCK TABLE capital  WRITE ' but it is not allowed in the version of MySql that I am using.    Below are the commands for the procedure (it works) and information on my MySql version/engine running.

DELIMITER $$

DROP PROCEDURE IF EXISTS `rt_20071001_ov03`.`test` $$
CREATE PROCEDURE `test`(in capitalUsed int)
BEGIN
SELECT pendingCapitalAvailable FROM capital FOR UPDATE;
UPDATE capital SET pendingCapitalAvailable = pendingCapitalAvailable - capitalUsed;
SELECT marginAvailable FROM capital FOR UPDATE;
UPDATE capital SET marginAvailable = marginAvailable - capitalUsed;
END $$

DELIMITER ;


Server info:
MySQL 5.0.45-community-nt via TCP/IP
MySQL Client Version 5.1.11
InnoDB tables but I could use MYISAM tables if necessary
jmokrauerAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>so only one computer can run the procedure at a time.
the implicit transaction on the UPDATE will take care of that, ie the table update, as it runs on a single row, I assume, will run sequentially.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
why not a single update:

DELIMITER $$

DROP PROCEDURE IF EXISTS `rt_20071001_ov03`.`test` $$
CREATE PROCEDURE `test`(in capitalUsed int)
BEGIN
  UPDATE capital SET pendingCapitalAvailable = pendingCapitalAvailable - capitalUsed, marginAvailable = marginAvailable - capitalUsed;
END $$

DELIMITER ;


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ps: I see that you actually got "that" solution already in your previous question:
http://www.experts-exchange.com/Programming/Languages/MatLab/Q_22880042.html

what is wrong about it?
0
 
jmokrauerAuthor Commented:
i need to make sure the table or rows are locked so only one computer can run the procedure at a time.  FYI: There are other tables on the same database that i need to leave unlocked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.