[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How to Lock a Table - MySql

Posted on 2007-10-08
Medium Priority
Last Modified: 2008-01-09
I have a table called 'matrix' in mysql.  I am subtracting 10 from a field called 'dollarsGained'.  To accomplish this, I will use the following code:

 UPDATE matrix SET dollarsGained = dollarsGained - 10;

I am having trouble with the locking portion of this code and would like the answers to the following questions:

1) After executing the statement ' LOCK TABLES matrix WRITE;' how do I check that the table is actually locked?
2) How do I just unlock the table 'matix' without unlocking all of the tables in the database?
3) How do I check that the table is actually unlocked?

Server info:
MySQL 5.0.45-community-nt via TCP/IP
MySQL Client Version 5.1.11
MYISAM tables
Question by:jmokrauer
LVL 21

Accepted Solution

Julian Matz earned 1500 total points
ID: 20038422
I haven't personally worked with this before, but see if the following might help...

Expert Comment

ID: 20482078
You don't need all that locking because the query is atomic. There is no race condition problem here. Any query you send to MySQL is atomic.

If your change will need to affect other fields my advice is to use transactions and InnoDB. All you have to do is enable the InnoDB engine in the MySQL configuration file and change table type.

Author Comment

ID: 20484748
makes sense, thanks!

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

873 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