-
Praveenkumar Hulakund authored
USED TABLES. Analysis: ---------- Issue here is, LOCK table operation sometime acquired too weaker lock on the tables locked implicitly. LOCK TABLE on a view which uses stored function which uses some table acquires lock TL_READ on that table. As result, such LOCK TABLE operation allows concurrent insert on the table used by function. And this can break statement based replication/ cause wrong binlog order. While opening tables used in a statement, tables used in stored functions(if statement uses any) are also opened and locked implicitly. When statement based replication is on, if function does not modify any data then the lock TL_READ acquired on such user tables by ignoring prelocking_placeholder set for them in function "read_lock_type_for_table". This helps in taking weaker locks on such tables and allows concurrent insert operations on them. But when opening tables for "LOCK TABLE" operations, because of the above mentioned logic, lock type "TL_READ" is set instead of "TL_READ_NO_INSERT" for tables of stored function which does not modify any data. Hence issue mentioned in the report is seen. Fix: ---------- In function "read_lock_type_for_table", when statement based replication is on, for tables of stored functions lock_type TL_READ_NO_INSERT is set for LOCK TABLE statement. Flag used to indicate stored function modifies data or not is ignored in this case.
Praveenkumar Hulakund authoredUSED TABLES. Analysis: ---------- Issue here is, LOCK table operation sometime acquired too weaker lock on the tables locked implicitly. LOCK TABLE on a view which uses stored function which uses some table acquires lock TL_READ on that table. As result, such LOCK TABLE operation allows concurrent insert on the table used by function. And this can break statement based replication/ cause wrong binlog order. While opening tables used in a statement, tables used in stored functions(if statement uses any) are also opened and locked implicitly. When statement based replication is on, if function does not modify any data then the lock TL_READ acquired on such user tables by ignoring prelocking_placeholder set for them in function "read_lock_type_for_table". This helps in taking weaker locks on such tables and allows concurrent insert operations on them. But when opening tables for "LOCK TABLE" operations, because of the above mentioned logic, lock type "TL_READ" is set instead of "TL_READ_NO_INSERT" for tables of stored function which does not modify any data. Hence issue mentioned in the report is seen. Fix: ---------- In function "read_lock_type_for_table", when statement based replication is on, for tables of stored functions lock_type TL_READ_NO_INSERT is set for LOCK TABLE statement. Flag used to indicate stored function modifies data or not is ignored in this case.
Loading