Note: In this post, I always refer to MariaDB. As far as I know, the techniques and the limitation I’m describing here also apply to Percona Server and MySQL, including 5.6. But since I didn’t test the examples, I just refer to MariaDB (tested on 5.5).
A query is a SQL statement that returns some results (
A Stored Program is a Stored Routine, a Trigger or an Event.
A Stored Routine is a Stored Procedure or a Stored Function.
A Dynamic SQL statement is astring composed by a Stored Program, which will be executed as a Prepared Statement. Even within normal Prepared Statements you can insert some values, for example you can “pass” them an ID which will be used in the
WHERE clause. But there are many limitations: you can’t compose the
ORDER BY clause, or decide what columns should be
SELECTed, for example. An example of Dynamic SQL is:
SET @sql = CONCAT('SELECT `', @col_name, '` AS `value` FROM `', @tab_name, '` LIMIT 1;');
MariaDB cursors can’t be used with Dynamic SQL. Their syntax is:
DECLARE <cursor_name> CURSOR FOR <query>
But is a hard-coded SQL statement, and can not be a variable. So, if you can’t hard-code your query because it should contain some variable parts, you can’t use a cursor to read the result set.
Hey, wait a moment… can’t you? Well, actually you can. You must write some code and use some dirty tricks, but you can.
The solution (Code)
Here’s the code we will use to implement “Dynamic Cursors”. I will explain it later:
DELIMITER || DROP PROCEDURE IF EXISTS `dyn_lock`; CREATE PROCEDURE `dyn_lock`(IN `query` TEXT, IN `lock_name` CHAR(64), IN `timeout` TINYINT UNSIGNED) NO SQL LANGUAGE SQL COMMENT 'Get a lock for dyn cursor' BEGIN -- output from GET_LOCK() DECLARE lock_res TINYINT UNSIGNED; -- error in query DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = '[dyn_lock] Dynamic SQL returned an error'; -- get lock or exit with err SET lock_res = GET_LOCK(`lock_name`, IFNULL(`timeout`, 5)); IF (lock_res IS NULL) THEN SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = '[dyn_lock] Could not acquire lock: Unknown error'; ELSEIF (lock_res = 0) THEN SIGNAL SQLSTATE VALUE '45000' SET MESSAGE_TEXT = '[dyn_lock] Could not acquire lock: Timeout expired'; END IF; -- create dynamic view SET @dyn_sql = CONCAT('CREATE OR REPLACE VIEW `', lock_name, '` AS ', query, ';'); PREPARE stmt_dyn_view FROM @dyn_sql; EXECUTE stmt_dyn_view; DEALLOCATE PREPARE stmt_dyn_view; END; DROP PROCEDURE IF EXISTS `dyn_cursor`; CREATE PROCEDURE `dyn_cursor`(IN `query` TEXT, IN `timeout` TINYINT UNSIGNED, OUT x TEXT) READS SQL DATA LANGUAGE SQL COMMENT 'Simulate dynamic cursor' BEGIN -- we assume there is only 1 column to be read; -- TEXT is good for any type DECLARE val TEXT; -- no more rows flag DECLARE eof BOOL; -- select data from a view. only the view is really dynamic DECLARE cur_dyn CURSOR FOR SELECT * FROM dyn_view; -- set eof flag DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = TRUE; CALL `dyn_lock`(`query`, 'dyn_view', `timeout`); -- open cursor on view OPEN cur_dyn; -- now that we have the cursor, we can release lock DO RELEASE_LOCK('dyn_view'); lp_dyn: LOOP IF eof = TRUE THEN LEAVE lp_dyn; END IF; FETCH cur_dyn INTO val; END LOOP; CLOSE cur_dyn; SET x = val; END; || DELIMITER ;
Just few notes before explaining this technique:
lp_dyn loop, in the
PROCEDURE, is the part where we do something with the cursor we created. I could have leave out that part because it’s not really part of the general solution, but I needed some code which demonstrates that my solution works. If you leave out that part, then you don’t need the
OUT parameter and the
val local variable.
* The user should call
dyn_lock(). The latter is called by the former. This will be explained later.
dyn_cursor() properly works if you pass a query which only produces 1 column. The last value returned by the query is assigned to the
SELECT the variable you pass, to check that your query works and the result set has been correctly read by the cursor.
* To test the procedure, you can
CALL something like these (call them from different connections to test concurrency):
CALL `dyn_cursor`('SELECT `ENGINE` FROM `information_schema`.`ENGINES`', NULL, @var1); CALL `dyn_cursor`('SELECT 1 AS `uno` FROM `information_schema`.`TABLES`', NULL, @var2); CALL `dyn_cursor`('SELECT -;', NULL, @var2);
The solution (Theory)
The technique is quite simple. Here’s the work flow.
The dynamic query is received through the
IN parameter. This happens in my example – please, note that in real-world examples, you could compose that string in the same
PROCEDURE. Or it could be a
FUNCTION, instead of a
We need to acquire a lock, so we call
dyn_lock(). It is not the table-level lock you would acquire with a
LOCK TABLE statement, but another type of lock. It’s more like a logical named lock entirely handled by four SQL functions (
IS_FREE_LOCK()). This kind of locks is interesting, because you may want to acquire locks that are not bound to a table, or you are using techniques that are affected by table locks in a strange way (transactions, triggers, etc). I will not fully explain the mechanism in this post, but here’s what we do. We acquire a lock named
GET_LOCK(). If that lock has been acquired by another connection, we wait for the number of seconds specified as
`timeout` parameter. If
NULL, the default value we decide is 5 (please, use the value you think is most appropriate in your situation). We could define two default values, one in
dyn_cursor() and one in
dyn_lock(). Later, the reason for this will be clear.
If the lock can not be acquired,
dyn_lock() throws an error. The
SQLSTATE '45000' is the one suggested by the MySQL documentation for user errors. The message is not complete, because this is just an example. But it should contain the name of the Stored Routine that thrown it, if you want make debug easier.
dyn_lock() creates a view with the specified name. And then the execution exits from
dyn_lock(). Here we open the cursor (alas, this can’t be done within
dyn_lock() because cursors exist at a Routine level), and we release the lock using
Let’s talk about the view. Its AS clause is the statement passed through `query` parameter. So, doing a
SELECT * on that view is like directly running `query`. The performances are the same, or almost the same. The advantage is that
SELECT * FROM `dyn_view` can be hard-coded in a cursor definition (we did it in
`query` is a string that can be generated dynamically.
Views can not be temporary in MariaDB/MySQL (they can in Postgres). This means that when you create/replace a view, other connections could use it. In our case, we must prevent a concurrency like this:
1) A creates
2) Just 0.0000001 seconds after, B replaces
3) A executes the query on
`dyn_view`, knowing not that it has been replaced.
To avoid this, we use the lock I described.
We want to release the lock as soon as possible, to increase performances. We can do this just after opening the cursor. It will work on a query, not on the view itself – so, we don’t need it anymore, and can allow other connections to replace it. In fact, the view is always created (if it doesn’t still exists) or replaced. It is never drop, to speed up things.
It is important that the view and the lock have the same name and are created within
dyn_lock(). This way,
dyn_lock() contains (part of) the generalized code. Not only
dyn_cursor(), but also other Stored Routines, can call
dyn_cursor() to get a lock and a view with a given name. If several Routines call
dyn_cursor() passing the the same name, they can’t be executed at the same time. For the sake of brevity, I will not show here this case here, but it can be useful. If different Routines call
dyn_cursor() with different names, they can be executed at the same time. But still two connections can not execute the same Routine at the same time. However, it is important that Routines release the lock name they acquired as soon as possible (again, see
To Do (or not To Do)
GET_LOCKS() accepts a timeout in seconds, not microseconds. For short queries, you may want to specify a value like 0.5. Or 1.5. If this limitation is a problem for you, you may consider to re-implement
dyn_lock() and write an unlocking
PROCEDURE. You could acquire locks by inserting your
CONNECTION_ID() into a
MEMORY (but not temporary!) table, and release the lock by deleting that ID. To check if a lock has already been acquired by someone else, you could use a
SELECT ... FOR UPDATE. But there is a complication: a query could be
KILLed before it releases its lock. So, you also need to write in the table a
TIMESTAMP that indicates when the lock has been acquired. After a given timeout (which can be constant or variable), the lock expires.
If you re-implement the locking system, and you have a high concurrency, you may also consider to use more than one view. The
SELECTs on the view must be hard-coded, so the number of allowed views and their names must be hard-coded too. But you could allow 3 view called
dyn_view3, and create 3 locks with the same names in the
MEMORY table. But if you use the
RELEASE_LOCK() functions, you need to use only 1 lock, and thus only 1 view.
Please, note that re-implementing the locking system will improve concurrency and timeout granularity, but will add complexity, and thus should reduce the performances in an environment where concurrency is almost irrilevant.
For the sake of brevity, errors generated by the Dynamic SQL are not handled here. But you should handle them.
Limitations and Feature Requests for MariaDB
* WARNING: because of a bug, in MySQL you must avoid to pass negative numbers to
GET_LOCK() as timeout, because it is an endless timeout. In MariaDB, this only happens if you pass very low negative numbers (MDEV-4017).
* First of all: if MariaDB adds support for cursors on dynamic SQL statements, all this code will no longer be necessary.
* LIMITATION: This technique can not be used recursively – that is, the query you pass to
dyn_cursor() can not contain a call to
dyn_cursor(). This is because
dyn_lock() uses a Prepared Statement. MariaDB/MySQL’s Prepared Statements exist at a connection-level, and are not recursive. I hope that this in the future we will have a way to implement this type of recursivity.
GET_LOCK() should accept a timeout argument with granularity in microseconds.
* Dynamic SQL could generate errors or warnings. In that case, it would be useful to get an error message specifying the name of the Prepared Statement and the Stored Routine that thrown it.
* If a Stored Routine could return a cursor or a result set, we could move the
DO RELEASE_LOCK() lines into
dyn_lock(). If we could do this, the code would be easier to maintain.
I use a lot of Stored Programs, and I am working (when I have the time to do it) on a couple of libraries I would like to release publicly. But there are strong limitations on what you can do with Routines, at the moment. I will write a post about what features I think that could make a developer’s life easier. Maybe some of them seem useless, until one starts to use Routines in real life – then, you understand that they could help you writing better (mantainable, general, faster) code. And maybe some of them are not so difficult to implement… at least, I hope so.