MySQL Internals Manual  /  Stored Programs  /  Stored Program Caches

16.6 Stored Program Caches

The goal of the Stored Program cache is to keep a parsed sp_head in memory, for future reuse. Reuse means:

  • To be able to execute concurrently the same Stored Program in different THD threads,

  • To be able to execute the same Stored Program multiple times (for recursive calls) in the same THD thread.

To achieve this, the implementation of sp_head must be both thread-safe and stateless. Unfortunately, it is neither:

  • The class sp_head is composed of sp_instr instructions to represent the code, and these instructions in turn depend on Item objects, used to represent the internal structure of a statement. The various C++ Item classes are not currently thread-safe, since the evaluation of an Item at runtime involves methods like Item::fix_fields(), which modify the internal state of items, making them impossible to safely evaluate concurrently.

  • The class sp_head itself contains attributes that describe the SQL logic of a Stored Program (which are safe to share), mixed with attributes that relate to the evaluation of this logic in a given instance to a Stored Program call (mostly the MEM_ROOT memory pool used during execution), which by definition cannot be shared.

The consequence of these restrictions is less than optimal code. What is currently implemented in the server is detailed in the following subsections, to help maintenance.


Needless to say, the current implementation of Stored Program caching is by no mean final, and could be re factored in future releases.

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