CODEWITHSUNDEEP
mysql
Mark Galeck
Mark Galeck

Reputation: 6405

how to lock a table in MySQL but not affect other tables

I want to lock a table in MySQL for the purpose that other processes cannot access that table while I have it locked.

The MySQL docs says this about LOCK TABLES command:

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them.

Good, so I try to do 

mysql>LOCK TABLES foo WRITE;

and I can check that other processes cannot then access foo.

The problem is that I cannot access other tables. The documentation says:

A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement. While the locks thus obtained are held, the session can access only the locked tables

This is very strange. Why am I forced to also lock other tables. I only want to prevent others from accessing foo. I do not want to lock anything else, I just want to access other tables as normal.

How to just lock one table and not change anything about other tables??

Upvotes: 3

Views: 1628

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562911

LOCK TABLES has no storage engine dependency, because it is handled by the SQL layer, above the storage engine layer. Row locks are handled within the InnoDB storage engine.

Sorry that MySQL's architecture is so complex. This is the advantage and the disadvantage of supporting multiple storage engines. Most RDBMS products don't have this capability, so all storage features appear more integrated into the rest of the functionality. In MySQL, the code for storage engines is independent from the storage-independent code, and some features may be implemented with certain storage engines.

Transactions are a good example. InnoDB supports transactions, and this defines the scope for InnoDB locking. When the transaction ends, all InnoDB-managed locks are released.

By default, the MySQL client tool and most programming interfaces operate in "autocommit" mode. So every SQL statement starts a new transaction and automatically commits it when the query finishes.

But you can control when transactions start and finish. See http://dev.mysql.com/doc/refman/5.6/en/commit.html

Upvotes: 0

Mark Galeck
Mark Galeck

Reputation: 6405

Being that this LOCK TABLES feature behaves in this (strange in my opinion) way, one way to handle this, is not use this process for anything else. That is, spawn a process just to lock this table, access this table, and unlock it. Use other processes to access other tables - the restriction to not be able to access other tables is not global as such, it is just limited to that one process.

After that, of course, I still have to synchronize control flow between the multiple processes I just created. But that is out of MYSql, I can just use whatever programming language I am using, which has such facilities.

I guess another way, would be to create a new database and put that one table in the new database.

Upvotes: 0

Related Questions