Search This Blog

23 October 2007

The context of external language procedure's queries

Perhaps it wasn't entirely clear what the last posting meant by "reentrant". It means that the external language stored procedure can issue a query within the same thread context that executed the stored procedure.

Thanks to the question posed by Bill Karwin, I have tested this scenarios to demonstrate:
mysql> lock table mysql.proc write;
Query OK, 0 rows affected (0.00 sec)

mysql> call test2('select distinct name from mysql.proc');
+--------------------+
| name |
+--------------------+
| test1 |
| test2 |
| xml_current_time |
| xml_get_state |
| xml_list_methods |
| xml_list_signature |
| xml_method_help |
| xml_sum_and_diff |
+--------------------+
8 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
As you can see, the exclusive write lock on the PROC table has not prevented the inline query from being able to read the table as it truly is executed within the same thread.
A second example to demonstrate that the stored procedure is executed from within the same transaction context:
mysql> create table foo (a int primary key) engine=innodb;
Query OK, 0 rows affected (0.19 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values (1),(2),(4);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> call test2('commit');
Query OK, 0 rows affected (0.01 sec)

mysql> insert into foo values (8),(12),(14);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> call test2('rollback');
Query OK, 0 rows affected (0.00 sec)

mysql> select * from foo;
+---+
| a |
+---+
| 1 |
| 2 |
| 4 |
+---+
3 rows in set (0.00 sec)

I hope that clarifies things a little.

1 comment:

Bill Karwin said...

Ok, cool, thanks for answering my questions. I had the questions about transaction context because I have seen people try to do this kind of thing using other kinds of tricks.

For example, a UDF that opens a new connection and executes a SQL query. In those cases, it's too easy to write an app that deadlocks against itself.

But because your implementation uses the current thread, it has less risk of these troubles. Congrats!