Bug #22412 Events: strange locking behavior

• Submitted: 16 Sep 2006 8:23
• Modified: 28 Sep 2006 10:41
• Reporter: Dmitry Lenev
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.1.12-debug-bk
• OS: Linux (Linux Suse 10.1)
• Assigned to: Andrey Hristov
• CPU Architecture: Any

[16 Sep 2006 8:23] Dmitry Lenev

Description:
This report is follow-up for the bug#16417.

Event which is executed every second and which does inserts into MyISAM table
which take exactly one second blocks selects on this table.
Note that this does not happen if one runs stored procedure which just does
the same inserts in the loop.

How to repeat:
create table t_24 (s1 timestamp, s2 int);
create event e_24 on schedule every 1 second on completion preserve do insert into t_24 select current_timestamp,sleep(1);
set global event_scheduler = 1;
# This will hang until scheduler is stopped
select * from t_24;

[16 Sep 2006 11:04] Valeriy Kravchuk

Thank you for a problem report. Verified just as described with 5.1.12-BK (ChangeSet@1.2330, 2006-09-16 07:19:42+02:00) on Linux.

[28 Sep 2006 10:40] Andrey Hristov

Seems to me like not a bug.
1. According to mysqladmin debug, the SELECT is low priority and the INSERTs are high priority. Moreover, the INSERTs sleep for one second and more are spawned than finishing. This keeps a constant list of high priority writing threads which do not allow the SELECT to finish.
- Changing the table type to Innodb solves the problem
- Stating explicitly that the INSERT should be LOW_PRIORITY and the SELECT should be HIGH_PRIORITY solves the problem - the SELECT query returns, not immediately but the response time is under 1s.

[28 Sep 2006 10:41] Andrey Hristov

Thread database.table_name          Locked/Waiting        Lock_type

0       mysql.slow_log              Locked - write        Concurrent insert lock
0       mysql.general_log           Locked - write        Concurrent insert lock
1       db2.t_24                    Waiting - read        Low priority read lock
10      db2.t_24                    Locked - write        High priority write lock