16.7.5 Call Nesting

In the following example, the Stored Procedure proc_1 makes a nested call to proc_2.

CREATE TABLE my_debug(
  seq int NOT NULL AUTO_INCREMENT,
  msg varchar(80),
  PRIMARY KEY(seq)
);

delimiter $$
    CREATE PROCEDURE proc_1()
BEGIN
  INSERT INTO my_debug(msg) VALUES ("entering p1");
  CALL proc_2();
  INSERT INTO my_debug(msg) VALUES ("leaving p1");
END$$

CREATE PROCEDURE proc_2()
BEGIN
  INSERT INTO my_debug(msg) VALUES ("inside p2");
END$$

delimiter ;
    CALL proc_1();
Tip

Oh, yes, we don't have a debugger yet, so this is old school printf-like debugging into a table.

By setting a breakpoint in mysql_insert in the server, the current thread stack at the first insert will look like this:

#0  mysql_insert () at sql_insert.cc:351
#1  in mysql_execute_command () at sql_parse.cc:2643
#2  in sp_instr_stmt::exec_core () at sp_head.cc:2609
#3  in sp_lex_keeper::reset_lex_and_exec_core () at sp_head.cc:2455
#4  in sp_instr_stmt::execute () at sp_head.cc:2560
#5  in sp_head::execute () at sp_head.cc:1077
#6  in sp_head::execute_procedure () at sp_head.cc:1726
#7  in mysql_execute_command () at sql_parse.cc:3807
#8  in mysql_parse () at sql_parse.cc:5274
#9  in dispatch_command () at sql_parse.cc:896
#10 in do_command () at sql_parse.cc:662
#11 in handle_one_connection () at sql_connect.cc:1089
#12 in start_thread () from /lib/libpthread.so.0
#13 in clone () from /lib/libc.so.6

By the time the second INSERT is executed, the stack will look like this:

#0  mysql_insert () at sql_insert.cc:351
#1  in mysql_execute_command () at sql_parse.cc:2643
#2  in sp_instr_stmt::exec_core () at sp_head.cc:2609
#3  in sp_lex_keeper::reset_lex_and_exec_core () at sp_head.cc:2455
#4  in sp_instr_stmt::execute () at sp_head.cc:2560
#5  in sp_head::execute () at sp_head.cc:1077
#6  in sp_head::execute_procedure () at sp_head.cc:1726
#7  in mysql_execute_command () at sql_parse.cc:3807
#8  in sp_instr_stmt::exec_core () at sp_head.cc:2609
#9  in sp_lex_keeper::reset_lex_and_exec_core () at sp_head.cc:2455
#10 in sp_instr_stmt::execute () at sp_head.cc:2560
#11 in sp_head::execute () at sp_head.cc:1077
#12 in sp_head::execute_procedure () at sp_head.cc:1726
#13 in mysql_execute_command () at sql_parse.cc:3807
#14 in mysql_parse () at sql_parse.cc:5274
#15 in dispatch_command () at sql_parse.cc:896
#16 in do_command () at sql_parse.cc:662
#17 in handle_one_connection () at sql_connect.cc:1089
#18 in start_thread () from /lib/libpthread.so.0
#19 in clone () from /lib/libc.so.6

In this stack trace, sp_head::execute_procedure() at #12 corresponds to CALL proc_1();, whereas sp_head::execute_procedure() at #6 corresponds to CALL proc_2();. In other words, recursive calls in the user SQL code are implemented by performing matching recursive calls in the system C++ code (the server).

This is actually a severe limitation of the implementation, which causes problems for the following reasons:

  • User logic can be arbitrarily nested, with a long chain of Stored Programs calling other Stored Programs. The total depth of calls can be greater than one would expect, especially considering that a VIEW can invoke a FUNCTION, and that a TRIGGER can also invoke other PROCEDURE, FUNCTION, or TRIGGER objects.

  • The amount of memory that can be consumed in the stack for a thread is not infinite. In fact, it's quite limited because {MAX NUMBER OF THREADS} * {MAX THREAD STACK} = {TOTAL STACK}. Note the catch in the equation here: MAX thread stack, which is dependent on the nesting of stored program in the user SQL code, for the worst case. Since MySQL currently does not use a thread pool but is compiled sometimes with a BIG number of threads, this can be a problem affecting scalability.

  • As a result, the Stored Program interpreter has to protect itself against stack overflow. This is implemented by check_stack_overrun()

What should be implemented instead, is representing the user SQL stack on the C++ heap, and have the interpreter loop instead of making recursive calls.

There are also other good reasons to use the heap. For example, for error reporting, the current implementation has no way to tell that proc_2 was called from proc_1, since this data is not available to the code; it's hidden in the C++ stack.

Nesting calls also has some impact on SQL exception handlers. The member THD::spcont for the current thread is not pointing to a single sp_rcontext, but to a stack of runtime contexts. This is implemented internally as a linked list, with sp_rcontext::m_prev_runtime_ctx.

With the example used, when the code is executing proc_1, THD::spcont points to the runtime context for proc_1. When the code is inside proc_2, the current thread THD::spcont points to sp_rcontext{proc_2}, which member m_prev_runtime_ctx points to sp_rcontext{proc_1}. This chain allows a parent Stored Program to catch exceptions raised by children Stored Programs.


User Comments
Sign Up Login You must be logged in to post a comment.