maria-developers team mailing list archive

[worklog-noreply@xxxxxxxxxxxx]

-----------------------------------------------------------------------
                              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)