Running Mysql 5.1.57 on InnoDB, and have a database locking issue.
I have two separate sessions connected to a single database MySQL. On the first session, I run a long SELECT query (technically a slow query) on table A. Then on the other connection, I run a small UPDATE query on table B.
Edit: As described at the bottom of this topic, this issue also occurs when the tables/queries are in separate databases altogether, and is not limited to tables/queries are in the same database.
For some reason the update on table B will not complete until the long select query finishes, as if they are on the same connection. In addition, the "PROCESS LIST" shows the second query as "freeing items", while it is waiting to execute.
Are there any settings, or configuration issues that would be causing these queries to run sequentially rather than simultaneously?
Thanks in advance for the help.
Table A
CREATE TABLE `history` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`log` text NOT NULL,
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id_idx` (`user_id`),
CONSTRAINT `history_user_id_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=364398 DEFAULT CHARSET=utf8
Table B
CREATE TABLE `client` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`root_id` int(11) DEFAULT NULL,
`salesperson_id` int(11) DEFAULT NULL,
`name` varchar(127) DEFAULT NULL,
`notes` text,
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `root_id_idx` (`root_id`),
KEY `salesperson_id_idx` (`salesperson_id`),
CONSTRAINT `client_root_id_client_id` FOREIGN KEY (`root_id`) REFERENCES `client` (`id`) ON DELETE CASCADE,
CONSTRAINT `client_salesperson_id_user_id` FOREIGN KEY (`salesperson_id`) REFERENCES `user` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=757 DEFAULT CHARSET=utf8
Query 1:
SELECT *
FROM `history`
WHERE log LIKE '%a%' ORDER BY log ASC LIMIT 0, 20000; // (fictitious query to replicate the issue)
Takes about 10 seconds to run
Query 2:
UPDATE `client` SET name = 'Test Name' WHERE id = 24; // hangs until query 1 is complete
MySQL Version Information
+-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 1.0.16 | | protocol_version | 10 | | version | 5.1.57-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | unknown-linux-gnu | +-------------------------+------------------------------+
Note: This is an Amazon RDS server (m1.large)
Global InnoDB Variables
+---------------------------------+------------------------+ | Variable_name | Value | +---------------------------------+------------------------+ | have_innodb | YES | | ignore_builtin_innodb | ON | | innodb_adaptive_flushing | ON | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 2097152 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_size | 5882511360 | | innodb_change_buffering | inserts | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | /rdsdbdata/db/innodb | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_format | Antelope | | innodb_file_format_check | Barracuda | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | O_DIRECT | | innodb_force_recovery | 0 | | innodb_io_capacity | 200 | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 134217728 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /rdsdbdata/log/innodb | | innodb_max_dirty_pages_pct | 75 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 0 | | innodb_open_files | 300 | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_spin_wait_delay | 6 | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | ON | | innodb_stats_sample_pages | 8 | | innodb_strict_mode | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | innodb_use_sys_malloc | ON | | innodb_version | 1.0.16 | | innodb_write_io_threads | 4 | +---------------------------------+------------------------+
Connection Variables
+----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | max_connections | 623 | | max_user_connections | 0 | +----------------------+-------+
Issue Persists Across Separate Databases
Further testing has shown that we can recreate this issue on an entirely different database all together, which negates any issue about table locking. To recreate this, we setup Table A / Query 1 in one database, and Table B / Query 2 in an entirely separate database (on the same server). When query 1 is running, Query 2 waits until #1 is complete before finishing.
This leads me to believe there is something server-wide (OS level file locking?) that is causing this issue, not related to any one database or table. Any ideas?
User Grants
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS,
REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.*
TO 'username'@'%' IDENTIFIED BY PASSWORD 'password' WITH GRANT OPTION
SHOW GLOBAL VARIABLES LIKE 'innodb%';
and display that in your question.