The MySQL engine permits user level locks: the
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
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.
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.
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 (
Students). All fields are characters (
Students field is a
Run the attached program and feed it examples as shown below:
select * from tps where course ='transactions';
insert into tps values('TestCourse', 'Dr. MAJO', 'MWF', 25);
update tps set course='TestCourse' where professor='Dr. MAJO';
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.
Works in a multinational pharmaceutical company as an IT specialist. A freelance software developer and web designer.