Search This Blog

Loading...

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.
Post a Comment