Mysql – Tables locked(database locked) in MYSQL

MySQL

We are using MYSQL INNODB,After inserted one record in table,Locked database operations resulted in proxy and application threads to increase that ultimately caused the proxy server to not accept connections.I would like to know the root cause for this tables locking?

Best Answer

In my personal opinion debugging table locks in MySQL can be a pain in the *ss sometimes. Some queries that may give you some insights are:

Get a list of the locked tables

SHOW OPEN TABLES WHERE In_Use > 0;

Get a list of all processes using MySQL resources with corresponding queries

SHOW FULL PROCESSLIST;

Get specific information about the InnoDB storage engine including deadlocks

SHOW ENGINE INNODB STATUS;

Related Solutions

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.

MySql InnoDB tables are missing after restarting

If you have MySQL 5.6 running before, then the MySQL data is located in C:\Program Files\MySQL\MySQL Server 5.6\data\

If you had recently installed MySQL 5.7, that instance has its own MySQL data in C:\Program Files\MySQL\MySQL Server 5.7\data\.

You need to migrate the data from MySQL 5.6 into MySQL 5.7.

If you are not sure what what version of MySQL was actually running on your Windows Server, here are two things you must investigate:

INVESTIGATION #1

Goto to Windows Explorer
Navigate to C:\Program Files\MySQL

INVESTIGATION #2

For every folder under C:\Program Files\MySQL
- Go inside the subfolder MySQL 5.x
- Locate the file my.ini

Whichever folder has my.ini was in the installed version of MySQL

If none of them has my.ini, you have to dig a little deeper

INVESTIGATION #3

Click Start -> Control Panel -> Administrative Tools -> Services
Once the Services panel is open, scroll down alphabetically to MySQL.
Right click on MySQL
Choose Properties

This will show you the path to the MySQL binary it called and the my.ini associated with it. If it does not show a my.ini, then the my.ini should appear in the parent directory of the executable.

If this does not work, then you will have to look inside MySQL Workbench for the original connection settings you were connecting with.