MySQL Table Manager
The MySQL engine permits user level locks. This article explains how to manipulate these lock types.
Introduction
The MySQL engine permits user level locks: the GET_LOCK() and RELEASE_LOCK() functions. IS_FREE_LOCK() checks for locks. LockTable is the function dealing with locks from the user perspective; it will continually cycle until it gets a lock. The ReleaseTable function will deal with the release of a database user level lock. The LockTable and ReleaseTable functions with try / catch blocks can be used to safely deal with database exceptions. Functions will return true if everything goes well, or false if an exception occurred.
Background
The program connects to a test MySQL database using an ODBC connection string. The program takes transactions (query, insert, update) from the user and feeds them into an array. After entering each transaction, the user presses the Add Transaction button. After entering all the transactions, the user presses the Run button.
The program functions based on the locks from other transactions, simulated by two buttons in our program. The two buttons are Process Other Transactions to lock the table and End Other Transactions to unlock the table. The program tests for exclusive locks before processing transactions.
A table is freed by calling SELECT IS_FREE_LOCK('TPS') if it is locked. If the table is currently locked, we’ll retry after certain time by using Thread.Sleep(x);. If the table is not locked, we’ll execute the lock query SELECT GET_LOCK('TPS',x) to lock the table. Here, x is the timeout period.
The SELECT RELEASE_LOCK('TPS') query is applied to release locks.
ExecuteQueries is the main function that will perform all the processes. It makes a call to the LockTable function to lock the tables at user level. It initiates a Transaction object, places the queries against the database, commits or rollbacks the transaction, calls the ReleaseTable function, and returns the result from the execution operation.
Using the code
Download the code and create a table in the MySQL test database, named TPS, with the following schema (Course, Professor, Schedule, Students). All fields are characters (Text); the Students field is a number.
Run the attached program and feed it examples as shown below:
--Example query
select * from tps where course ='transactions';
--Example insert
insert into tps values('TestCourse', 'Dr. MAJO', 'MWF', 25);
--Example update
update tps set course='TestCourse' where professor='Dr. MAJO';
--Example delete
delete from tps where course='test';
After feeding the SQL statements, press Add Transaction, then press Process Another Transaction. Now press Run. The program will not run the SQL statements since the table is locked exclusively by another transaction. Press End Other Transaction, and now your SQL statement will run.