?
Solved

Locking a Table in a Stored Procedure

Posted on 2007-10-09
4
Medium Priority
?
2,116 Views
Last Modified: 2010-07-27
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
0
Comment
Question by:jmokrauer
  • 3
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20041005
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20041057
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
 

Author Comment

by:jmokrauer
ID: 20041292
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20041350
>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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question