Sergey Vojtovich added a comment - 2020-06-04 12:07 Well, an argument comes from gdb I guess: Thread 13 (Thread 0x7f24fe51f700 (LWP 770)): #0 pthread_cond_timedwait@@GLIBC_2.3.2 () at ../sysdeps/unix/sysv/linux/x86_64/pthread_cond_timedwait.S:225 #1 0x000055fd9c5521f3 in safe_cond_timedwait (cond=0x7f24cc000ce8, mp=0x7f24cc000c38, abstime=0x7f24fe51cc80, file=0x55fd9cb24bc0 "/home/svoj/devel/maria/mariadb/include/mysql/psi/mysql_thread.h", line=1257) at /home/svoj/devel/maria/mariadb/mysys/thr_mutex.c:546 #2 0x000055fd9bebd2e1 in inline_mysql_cond_timedwait (that=0x7f24cc000ce8, mutex=0x7f24cc000c38, abstime=0x7f24fe51cc80, src_file=0x55fd9cb25718 "/home/svoj/devel/maria/mariadb/sql/mdl.cc", src_line=1174) at /home/svoj/devel/maria/mariadb/include/mysql/psi/mysql_thread.h:1257 #3 0x000055fd9bebe9b7 in MDL_wait::timed_wait (this=0x7f24cc000c38, owner=0x7f24cc000be8, abs_timeout=0x7f24fe51cc80, set_status_on_timeout=false, wait_state_name=0x55fd9d6c54c0 <MDL_key::m_namespace_to_wait_state_name+192>) at /home/svoj/devel/maria/mariadb/sql/mdl.cc:1174 #4 0x000055fd9bec0877 in MDL_context::acquire_lock (this=0x7f24cc000c38, mdl_request=0x7f24fe51cd80, lock_wait_timeout=10) at /home/svoj/devel/maria/mariadb/sql/mdl.cc:2381 #5 0x000055fd9c10237c in Item_func_get_lock::val_int (this=0x7f24cc012b98) at /home/svoj/devel/maria/mariadb/sql/item_func.cc:4046 #6 0x000055fd9bf59e79 in Type_handler::Item_send_long (this=0x55fd9d8d79a0 <type_handler_slong>, item=0x7f24cc012b98, protocol=0x7f24cc0010e0, buf=0x7f24fe51d070) at /home/svoj/devel/maria/mariadb/sql/sql_type.cc:7170 #7 0x000055fd9bf681bc in Type_handler_long::Item_send (this=0x55fd9d8d79a0 <type_handler_slong>, item=0x7f24cc012b98, protocol=0x7f24cc0010e0, buf=0x7f24fe51d070) at /home/svoj/devel/maria/mariadb/sql/sql_type.h:5415 #8 0x000055fd9bbe6060 in Item::send (this=0x7f24cc012b98, protocol=0x7f24cc0010e0, buffer=0x7f24fe51d070) at /home/svoj/devel/maria/mariadb/sql/item.h:1056 #9 0x000055fd9bbdf805 in Protocol::send_result_set_row (this=0x7f24cc0010e0, row_items=0x7f24cc0126d8) at /home/svoj/devel/maria/mariadb/sql/protocol.cc:1082 #10 0x000055fd9bc98d01 in select_send::send_data (this=0x7f24cc013580, items=...) at /home/svoj/devel/maria/mariadb/sql/sql_class.cc:3002 #11 0x000055fd9bdaf0a7 in select_result_sink::send_data_with_check (this=0x7f24cc013580, items=..., u=0x7f24cc004b30, sent=0) at /home/svoj/devel/maria/mariadb/sql/sql_class.h:5279 #12 0x000055fd9bd655db in JOIN::exec_inner (this=0x7f24cc0135a8) at /home/svoj/devel/maria/mariadb/sql/sql_select.cc:4332 #13 0x000055fd9bd64e75 in JOIN::exec (this=0x7f24cc0135a8) at /home/svoj/devel/maria/mariadb/sql/sql_select.cc:4245 #14 0x000055fd9bd665a6 in mysql_select (thd=0x7f24cc000b18, tables=0x0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2149845760, result=0x7f24cc013580, unit=0x7f24cc004b30, select_lex=0x7f24cc012588) at /home/svoj/devel/maria/mariadb/sql/sql_select.cc:4669 #15 0x000055fd9bd56005 in handle_select (thd=0x7f24cc000b18, lex=0x7f24cc004a68, result=0x7f24cc013580, setup_tables_done_option=0) at /home/svoj/devel/maria/mariadb/sql/sql_select.cc:429 #16 0x000055fd9bd1b21d in execute_sqlcom_select (thd=0x7f24cc000b18, all_tables=0x0) at /home/svoj/devel/maria/mariadb/sql/sql_parse.cc:6207 #17 0x000055fd9bd124ea in mysql_execute_command (thd=0x7f24cc000b18) at /home/svoj/devel/maria/mariadb/sql/sql_parse.cc:3939 #18 0x000055fd9bd20145 in mysql_parse (thd=0x7f24cc000b18, rawbuf=0x7f24cc0124f0 "SELECT GET_LOCK(\"l1\", 10)", length=25, parser_state=0x7f24fe51e3a0, is_com_multi=false, is_next_command=false) at /home/svoj/devel/maria/mariadb/sql/sql_parse.cc:7991 #19 0x000055fd9bd0c2c2 in dispatch_command (command=COM_QUERY, thd=0x7f24cc000b18, packet=0x7f24cc008939 "", packet_length=25, is_com_multi=false, is_next_command=false) at /home/svoj/devel/maria/mariadb/sql/sql_parse.cc:1875 #20 0x000055fd9bd0a93e in do_command (thd=0x7f24cc000b18) at /home/svoj/devel/maria/mariadb/sql/sql_parse.cc:1356 #21 0x000055fd9beafee7 in do_handle_one_connection (connect=0x55fd9f6bc908, put_in_cache=true) at /home/svoj/devel/maria/mariadb/sql/sql_connect.cc:1411 #22 0x000055fd9beafbf7 in handle_one_connection (arg=0x55fd9f6bc908) at /home/svoj/devel/maria/mariadb/sql/sql_connect.cc:1313 #23 0x000055fd9c3ee3ae in pfs_spawn_thread (arg=0x55fd9f63cf88) at /home/svoj/devel/maria/mariadb/storage/perfschema/pfs.cc:2201 #24 0x00007f250ae916ba in start_thread (arg=0x7f24fe51f700) at pthread_create.c:333 #25 0x00007f250a12241d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109   Thread 12 (Thread 0x7f24fe569700 (LWP 767)): #0 0x00007f250ae93dac in __GI___pthread_mutex_lock (mutex=0x7f24d8000d50) at ../nptl/pthread_mutex_lock.c:80 #1 0x000055fd9c552da8 in rw_pr_rdlock (rwlock=0x7f24d8000d50) at /home/svoj/devel/maria/mariadb/mysys/thr_rwlock.c:282 #2 0x000055fd9bebcfa2 in inline_mysql_prlock_rdlock (that=0x7f24d8000d50, src_file=0x55fd9cb24d18 "/home/svoj/devel/maria/mariadb/sql/mdl.h", src_line=1086) at /home/svoj/devel/maria/mariadb/include/mysql/psi/mysql_thread.h:970 #3 0x000055fd9bec2f3a in MDL_context::lock_deadlock_victim (this=0x7f24d8000c38) at /home/svoj/devel/maria/mariadb/sql/mdl.h:1086 #4 0x000055fd9bebd714 in Deadlock_detection_visitor::opt_change_victim_to (this=0x7f24fe567780, new_victim=0x7f24d8000c38) at /home/svoj/devel/maria/mariadb/sql/mdl.cc:313 #5 0x000055fd9bebd66c in Deadlock_detection_visitor::leave_node (this=0x7f24fe567780, node=0x7f24d8000c38) at /home/svoj/devel/maria/mariadb/sql/mdl.cc:278 #6 0x000055fd9bec15bb in MDL_lock::visit_subgraph (this=0x55fd9efb81d0, waiting_ticket=0x7f24d8207a70, gvisitor=0x7f24fe567780) at /home/svoj/devel/maria/mariadb/sql/mdl.cc:2749 #7 0x000055fd9bec1607 in MDL_ticket::accept_visitor (this=0x7f24d8207a70, gvisitor=0x7f24fe567780) at /home/svoj/devel/maria/mariadb/sql/mdl.cc:2769 #8 0x000055fd9bec1678 in MDL_context::visit_subgraph (this=0x7f24d8000c38, gvisitor=0x7f24fe567780) at /home/svoj/devel/maria/mariadb/sql/mdl.cc:2796 #9 0x000055fd9bec16c7 in MDL_context::find_deadlock (this=0x7f24d8000c38) at /home/svoj/devel/maria/mariadb/sql/mdl.cc:2824 #10 0x000055fd9bec06e1 in MDL_context::acquire_lock (this=0x7f24d8000c38, mdl_request=0x7f24fe567910, lock_wait_timeout=86400) at /home/svoj/devel/maria/mariadb/sql/mdl.cc:2369 #11 0x000055fd9c1bb7be in Global_read_lock::lock_global_read_lock (this=0x7f24d80042b0, thd=0x7f24d8000b18) at /home/svoj/devel/maria/mariadb/sql/lock.cc:1074 #12 0x000055fd9bef3c74 in reload_acl_and_cache (thd=0x7f24d8000b18, options=16388, tables=0x0, write_to_binlog=0x7f24fe567ec0) at /home/svoj/devel/maria/mariadb/sql/sql_reload.cc:250 #13 0x000055fd9bd17d75 in mysql_execute_command (thd=0x7f24d8000b18) at /home/svoj/devel/maria/mariadb/sql/sql_parse.cc:5422 #14 0x000055fd9bd20145 in mysql_parse (thd=0x7f24d8000b18, rawbuf=0x7f24d8013950 "FLUSH TABLES WITH READ LOCK", length=27, parser_state=0x7f24fe5683a0, is_com_multi=false, is_next_command=false) at /home/svoj/devel/maria/mariadb/sql/sql_parse.cc:7991 #15 0x000055fd9bd0c2c2 in dispatch_command (command=COM_QUERY, thd=0x7f24d8000b18, packet=0x7f24d80086a9 "FLUSH TABLES WITH READ LOCK", packet_length=27, is_com_multi=false, is_next_command=false) at /home/svoj/devel/maria/mariadb/sql/sql_parse.cc:1875 #16 0x000055fd9bd0a93e in do_command (thd=0x7f24d8000b18) at /home/svoj/devel/maria/mariadb/sql/sql_parse.cc:1356 #17 0x000055fd9beafee7 in do_handle_one_connection (connect=0x55fd9f6bc908, put_in_cache=true) at /home/svoj/devel/maria/mariadb/sql/sql_connect.cc:1411 #18 0x000055fd9beafbf7 in handle_one_connection (arg=0x55fd9f6bc908) at /home/svoj/devel/maria/mariadb/sql/sql_connect.cc:1313 #19 0x000055fd9c3ee3ae in pfs_spawn_thread (arg=0x55fd9f63cf88) at /home/svoj/devel/maria/mariadb/storage/perfschema/pfs.cc:2201 #20 0x00007f250ae916ba in start_thread (arg=0x7f24fe569700) at pthread_create.c:333 #21 0x00007f250a12241d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109 These two threads represent FTWRL and SELECT GET_LOCK("l1", 10) waiting for each other. Good enough?

Rinat Ibragimov (Inactive) added a comment - 2020-06-08 21:49 svoj , I think I'm starting to understand what you said before. I was thinking that there was a disastrous internal error in server that is triggered by a seemingly innocent test scenario. That's why I tried to "fix" the scenario so it wouldn't be blocked by mysqltest tester. In the "fixed" scenarious everything went fine, and tests completed successfully. But it's not about an internal error. It's about user convenience. You want to protect users from shooting their foot by combining FTWRL with GET_LOCK's. Currently, if a user tries to make a deadlock by only calling GET_LOCK's in a conflicting way from different connections, they get a warning instead. But this safety net doesn't work when FTWRL or LOCK TABLES are involved. And you want that safety net to be present at all times. Is my understanding correct?

Rinat Ibragimov (Inactive) added a comment - 2020-06-18 14:24 There is a tight loop in FTWRL code path which tries to acquire read lock again and again. With introduction of another lock attempt from GET_LOCK() , FTWRL becomes a "victim" of the deadlock detector, but since it tries to reacquire lock again, nothing changes. It keeps spinning there. Although it's possible to tune deadlock weights for that particular case, deadlocks in other scenarios may appear. For example, making all weights the same will introduce a deadlock in DROP TABLE . So, to fix the issue with FTWRL spinning in a tight loop, dynamic weights are used. Each time a lock is selected as a victim, its dynamic weight is increased. That doesn't have any effect is that lock is then reset immediately. But if it's going to be reacquired, it may be selected again, as in FTWRL vs GET_LOCK() case. Eventually, its weight becomes high enough that the deadlock detector selects another lock as a target.

Michael Widenius added a comment - 2020-11-05 14:35 The pull request looks fine. However, as a reference I think it's good to document that possible ways to fix this bug (if it ever re-appears): 1) Increase weights of mdl locks if killed by deadlock handler. This helps with mdl locks that are re-acquired at once after a deadlock (like FTWRL and table locks) as it ensures that all conflicting mdl locks are aborted eventually. 2) Allow deadlock handler to kill FTWRL after a certain number of attempts By the way, we should check that FTWRL can be killed with 'kill'' while in the deadlock test loop in Global_read_lock::lock_global_read_lock(). If the code is safe, there should at least be a code comment that explains why. 3) Not allow FTWRL if one has user lock. The solution we are going to use is 1)

Michael Widenius added a comment - 2021-03-10 17:08 Review & commit