How to Lock a Table - MySql

Posted on 2007-10-08
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

    I haven't personally worked with this before, but see if the following might help...

    Expert Comment

    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

    makes sense, thanks!

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    I have been using r1soft Continuous Data Protection ( for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
    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 seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now