----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Reduced table/row locking for SELECT statement with read-only stored function CREATION DATE..: Thu, 24 Nov 2011, 10:55 SUPERVISOR.....: Monty IMPLEMENTOR....: Monty COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 244 (http://askmonty.org/worklog/?tid=244) VERSION........: Server-5.5 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: When statement (or mixed) binlogging is used, the server currently is overly conservative in its use of table/row locks for SELECT statements that call stored functions. For example: delimiter // CREATE FUNCTION `test_f`(x CHAR(8)) RETURNS INT READS SQL DATA BEGIN DECLARE output INT DEFAULT NULL; SELECT id FROM t1 WHERE a = x INTO output; RETURN output; END // delimiter ; SELECT test_f("foo"); In general, it is possible for a SELECT statement that calls a stored function to modify tables, and thus to need to be binlogged. In statement-based binlogging, this requires taking extra read locks to ensure that replication will apply events from concurrent queries on the master in the correct order, just like normal UPDATE statements. However, most uses of stored functions in SELECT do _not_ modify data, as in the above example. Thus there is no need for extra read locks in statement-based binlog mode, as nothing will be logged to the binlog anyway for the SELECT statement. However, the server currently takes the pessimistic safe route and _always_ takes extra read locks in statement-based binlogging when stored functions are used. There is no analysis to detect that stored functions used are read-only, and hence that no extra locking is needed. This worklog is about improving this so that in common cases where no table modification is done in stored functions, no extra locking is done, while still preserving correct operation for statement-based replication when modification _is_ done. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)