asked on

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

Guy Hengel [angelIII / a3]

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 ;

Guy Hengel [angelIII / a3]

ps: I see that you actually got "that" solution already in your previous question:
https://www.experts-exchange.com/questions/22880042/Stored-Procedure-MySql.html

what is wrong about it?

jmokrauer

ASKER

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial