Bug #95367 Bug 29772622 - LOCK_GRANT AND LOCK_OPEN CAN DEADLOCK ON A GTID SLAVE II
Submitted: 14 May 2019 9:00 Modified: 7 Feb 2020 7:07
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.7.26 OS:Any
Assigned to: CPU Architecture:Any

[14 May 2019 9:00] Shane Bester
Description:
This is a followup to the previous bug:
Bug 28293047 - LOCK_GRANT AND LOCK_OPEN CAN DEADLOCK ON A GTID SLAVE 
http://bugs.mysql.com/bug.php?id=91548

Found another situation of deadlock.  Below the thread stacks:

Base: g:\ade\build\sb_0-33648028-1555169098.48\mysql-5.7.26

00 ntdll!NtWaitForKeyedEvent
01 ntdll! ?? ::FNODOBFM::`string'
02 mysqld!Partitioned_rwlock::rdlock [.\auth\partitioned_rwlock.h @79]
03 mysqld!fill_effective_table_privileges [.\sql_authorization.cc @ 3771]
04 mysqld!TABLE_LIST::prepare_security [.\sql\table.cc @ 5663]
05 mysqld!open_and_read_view [.\sql\sql_view.cc @ 1188]
06 mysqld!open_table [.\sql\sql_base.cc @ 3442]
07 mysqld!open_and_process_table [.\sql\sql_base.cc @ 5173]
08 mysqld!open_tables [.\sql\sql_base.cc @ 5784]
09 mysqld!open_tables_for_query [.\sql\sql_base.cc @ 6560]
0a mysqld!execute_sqlcom_select [.\sql\sql_parse.cc @ 5115]
0b mysqld!mysql_execute_command [.\sql\sql_parse.cc @ 2816]
0c mysqld!Prepared_statement::execute [.\sql\sql_prepare.cc @ 3977]
0d mysqld!Prepared_statement::execute_loop [.\sql_prepare.cc @3585]
0e mysqld!mysql_sql_stmt_execute [.\sql\sql_prepare.cc @ 2632]
0f mysqld!mysql_execute_command [.\sql\sql_parse.cc @ 2833]
10 mysqld!mysql_parse [.\sql\sql_parse.cc @ 5576]
11 mysqld!dispatch_command [.\sql\sql_parse.cc @ 1487]
12 mysqld!do_command [.\sql\sql_parse.cc @ 1027]
13 mysqld!handle_connection [.\connection_handler_per_thread.cc @ 306]
14 mysqld!pfs_spawn_thread [.\perfschema\pfs.cc @ 2193]
15 mysqld!win_thread_start [.\mysys\my_thread.c @ 38]

 # Call Site
00 ntdll!ZwWaitForSingleObject+0xa
01 ntdll!RtlpWaitOnCriticalSection+0xe8
02 ntdll!RtlEnterCriticalSection+0xd1
03 mysqld!check_if_table_exists [.\sql\sql_base.cc @ 2679]
04 mysqld!open_table [.\sql\sql_base.cc @ 3262]
05 mysqld!open_and_process_table [.\sql\sql_base.cc @ 5173]
06 mysqld!open_tables [.\sql\sql_base.cc @ 5784]
07 mysqld!open_and_lock_tables [.\sql\sql_base.cc @ 6504]
08 mysqld!open_n_lock_single_table [.\sql\sql_base.cc @ 6346]
09 mysqld!System_table_access::open_table [.\rpl_table_access.cc @57]
0a mysqld!Gtid_table_access_context::init [.\sql\rpl_gtid_persist.cc @197]
0b mysqld!Gtid_table_persistor::save [.\sql\rpl_gtid_persist.cc @ 411]
0c mysqld!Gtid_state::save [.\sql\rpl_gtid_state.cc @ 716]
0d mysqld!commit_owned_gtids [.\sql\handler.cc @ 1593]
0e mysqld!commit_owned_gtid_by_partial_command [.\sql\handler.cc @ 1623]
0f mysqld!mysql_drop_user [.\sql\auth\sql_user.cc @ 1619]
10 mysqld!mysql_execute_command [.\sql\sql_parse.cc @ 3969]
11 mysqld!mysql_parse [.\sql\sql_parse.cc @ 5576]
12 mysqld!Query_log_event::do_apply_event [.\sql\log_event.cc @ 4791]
13 mysqld!Log_event::apply_event [.\sql\log_event.cc @ 3473]
14 mysqld!apply_event_and_update_pos [.\sql\rpl_slave.cc @ 4734]
15 mysqld!exec_relay_log_event [.\sql\rpl_slave.cc @ 5283]
16 mysqld!handle_slave_sql [.\sql\rpl_slave.cc @ 7463]
17 mysqld!pfs_spawn_thread [.\storage\perfschema\pfs.cc @ 2193]
18 mysqld!win_thread_start [.\mysys\my_thread.c @ 38] 

How to repeat:
[7 Feb 2020 7:07] Erlend Dahl
Fixed in 5.7.28, 8.0.18.

On a GTID-enabled server, concurrent execution of DROP USER and a
prepared statement that accessed a view could deadlock.