Search This Blog

23 October 2007

Reentrant MySQL with External Stored Procedures

Ok, this is a late late blog post because the code was done and pushed into the work tree not last weekend, but the weekend before. I think it is a pretty significant feature to blog about but for some strange reason, it completely slipped my mind.

Check this out:
mysql> call test2('select count(*) from mysql.user; select count(*) from mysql.proc');
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
Not impressed yet? How about this:
mysql> set max_sp_recursion_depth=2;
Query OK, 0 rows affected (0.00 sec)

mysql> call test2('select count(*) from mysql.proc;call test2("select count(*) from mysql.db;call test2(\'select count(*) from mysql.user\')")');
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
And here was how the procedure was declared:

CREATE PROCEDURE test2(query text)
READS SQL DATA
LANGUAGE deep_thought
DYNAMIC RESULT SETS 5
EXTERNAL NAME 'loopback';
Maybe now you're curious how the language plugin performs this magic? Well, the psm_example.so plugin is actually linked against the MySQL client libraries, albeit a craftily modified version of it: Less than 10 lines of code within the client library was added. Then the stored routine was able to reenter the mysql server simply by using the mysql client library and execute statements within the same context by having code somewhat like the following:

mysql_options(&mysql, MYSQL_OPT_USE_INLINE_CONNECTION, NULL);
if (!mysql_real_connect(&mysql, 0, 0, 0, 0, 0, 0, CLIENT_MULTI_STATEMENTS))
{ ... }

Pretty nifty, innit?

3 comments:

Bill Karwin said...

Consider implications for deadlocks with this type of system.

Say you have a lock on some table, and then you attempt to use the same table in a query executed using the reentrant procedure. When the procedure's connection attempts to take out a lock on the table, what happens? Does the application deadlock against its own lock?

Here's another question: how is transaction isolation handled? If the procedure needs to create its own transaction context for the reentrant query, does this have implications for rollback and also repeatable read transactions?

Xaprb said...

At first glance, this doesn't appear to allow multiple connections to participate in a single transaction, but are there any possibilities for other interesting things like that?

Antony said...

On 24 Oct, 2007, at 10:26, Xaprb wrote:

> At first glance, this doesn't appear to allow multiple connections to participate in a single transaction, but are there any possibilities for other interesting things like that?

Unlikely, that is something completely different: That is in the realm of distributed transactions aka XA. Currently MySQL's XA support does not include SUSPEND/JOIN semantics

Regards,
Antony.