Check this out:
mysql> call test2('select count(*) from mysql.user; select count(*) from mysql.proc');Not impressed yet? How about this:
+----------+
| 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)
mysql> set max_sp_recursion_depth=2;And here was how the procedure was declared:
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)
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:
CREATE PROCEDURE test2(query text)
READS SQL DATA
LANGUAGE deep_thought
DYNAMIC RESULT SETS 5
EXTERNAL NAME 'loopback';
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:
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?
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?
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.
Post a Comment