How to list a locked table in mysql

[Viperan]

I try to Lock and unlock certain tables from my database test using JConnector (mysql jdbc lattest version) and I found out that the unlock command is not actually works. Now my database is occupied by these locked tables. How do I see a list of Locked tables in mysql and How do I get rid of that so I can unlock the tables...

thanks

daniel-

 

[ericbrunson]

Unless a global "unlock tables" works, you don't have much choice other than looking at the process list.

 

[Viperan]

The problem is that the UNLOCK TABLES command doesn't really work when executed from JDBC! Plus, the tables that hold the list of the locked tables in the database got copied when I dump the database into txt file. The last thing that I want to do is to open that txt file and tried to examine all the tables. so do you have any other alternatives.....


thanks


daniel-

 

[ericbrunson]

I would either have to use the mysql command line, or look through that text file. Just copy it to your machine with the most memory and load that sucker up in emacs. Or grep through it. Though I can't believe a lock would survive restarting mysqld.

 

[Viperan]

I try to restart the mysqld couple of times, no luck so far. I am sure that lock mechanism must be stored as a temporary table that also stored in the database table somewhere...

BTW, do you know any command to Unlock tables (besides UNLOCK TABLESthat executable from JDBC connector. I have the latest (stable version)of every Database components. I use mysql 4.0.21 client and server and I use JDBC ver 3.0.15GA

thanks for your help

daniel-

 

[ericbrunson]

I think you must be mistaken about the locks being stored in a table. I regularly sync my replication slaves by doing a "lock tables", mysqldump, "unlock tables". These imports never perpetuate the locks.

 

[Viperan]

I think you're right. I tried to search into the mysqldump file for all my Databases and I couldn't find a Lock table inside of it. I have another question though. Have you ever tried to lock tables from JDBC. If so can you Unlock it?

thanks

daniel-

 

[ericbrunson]

Java sucks, I only use it when I have a gun to my head.

 

[Viperan]

I agree. BTW, here is the Error Log that I got :


Logger:: logging DatabaseAccessor:: SQLException: Can not issue INSERT/UPDATE/DELETE with executeQuery()
Logger:: logging DatabaseAccessor:: SQLState: S1009
Logger:: logging DatabaseAccessor:: Query: UNLOCK TABLES
Logger:: logging DatabaseAccessor:: VendorError: 0

I am not sure why UNLOCK is not able to be executed from JDBC...

daniel-

 

[ericbrunson]

Perhaps you shouldn't be using executeQuery(). The error indicates that executeQuery will only take DQL (data query language). Insert/Update/Delete are DML (data manipulation language) as is Unlock.

 

[Viperan]

Thanks. It works...

daniel-

 

[ericbrunson]

But this begs the question, why are you locking the tables? It is unlikely that you really need to in the course of a standard application.

 

[Viperan]

well in My Database Agents, there is a task to update a user info and the user infos are spreaded to four different tables. Inorder to update these infos. The query must be done in seperated execution time, based on what info does the user inserted. thus these tables must be locked so that nobody delete the info in one of those four different tables. The lock mechanism guarantee that all the infos are intact during the update process. I know that we can solve this problem using store procedure. However I have to convert the whole database from MyISAM to InnoDB or BDB, which is slower.

daniel-

 

[ericbrunson]

Yes, similar to the penalty you pay to have the flexibility of a kinda-relational database compared to the speed of a gdb or berkeley db file, you take an additional hit in performance to get the ease of transactional integrity of InnoDB. It's a double edged sword.