MySQL Bugs: #33041: Cannot set FALCON_CONSISTENT

Bug #33041	Cannot set FALCON_CONSISTENT_READ for local session.
Submitted:	6 Dec 2007 17:23	Modified:	1 Oct 2008 16:37
Reporter:	Kevin Lewis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.4	OS:	Any
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any

Description:
Falcon has two methods of doing repeatable read.

1) Consistent Read - truly consistent. Transaction A cannot make changes to records that were updated or inserted by a transaction that started after Transaction A.

2) Write Committed - InnoDB compatible Repeatable Read. Transaction A can make changes to records that were updated or inserted by a transaction B that started after Transaction A. But this is done only after transaction B commits. While transaction B is active, Transaction A waits for it.

In the current 6.0.4, we introduced a setting called falcon_consistent_read. The default is currently ON. This work was done for Bug#29151 and is documented there.

We wanted to make this setting changeable;
* At engine start up;
--falcon-consistent-read=off
* For the current session;
SET SESSION FALCON_CONSISTENT_READ=OFF;
SET @@FALCON_CONSISTENT_READ=OFF;
Note that this would only affect new transactions that start after this change, not currently active transactions.

But we thought it was inappropriate to allow other sessions to change the default way of doing REPEATABLE_READ for active sessions, so it seemed like this should NOT be allowed.
SET GLOBAL FALCON_CONSISTENT_READ=OFF;

But this cannot be done with the current flags. In version 6.0.4, I did not use any flags, So currently these are possible;

--falcon-consistent-read=off
SET GLOBAL FALCON_CONSISTENT_READ=OFF;

But these are not;
SET FALCON_CONSISTENT_READ=OFF;
SET SESSION FALCON_CONSISTENT_READ=OFF;
SET @@FALCON_CONSISTENT_READ=OFF;
I even tried to use the flag PLUGIN_VAR_THDLOCAL to allow setting it locally, but that did not change anything.

How to repeat:
mysql> set @@falcon_consistent_read = OFF;
ERROR 1229 (HY000): Variable 'falcon_consistent_read' is a GLOBAL variable and should be set with SET GLOBAL
mysql> set session falcon_consistent_read = OFF;
ERROR 1229 (HY000): Variable 'falcon_consistent_read' is a GLOBAL variable and should be set with SET GLOBAL

Suggested fix:
The server needs to allow storage engines to declare variables that can be set at startup and for any local session.

Antony offered to make a change to the server to make this requested configuration possible, he reports that Serg rejected the patch. Instead, he suggests that we introduce a whole new transaction isolation level called CONSISTENT_READ.

I would argue against this because.
* Only Falcon would offer this isolation level. Other storage engines would probably return an error. Then just like myisam, the other engines would have to be downgraded to the level they can support. This requires changes to the server to make this work.
* The four standard isolation levels are widely documented and used. Adding a 5th isolation level will probably mean that it will not get used.

Thank you for the bug report.

c:\dev>6.0f\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.5-alpha-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set @@falcon_consistent_read = OFF;
ERROR 1229 (HY000): Variable 'falcon_consistent_read' is a GLOBAL variable and should be set with SET GLOBAL
mysql> set session falcon_consistent_read = OFF;
ERROR 1229 (HY000): Variable 'falcon_consistent_read' is a GLOBAL variable and should be set with SET GLOBAL
mysql> set global falcon_consistent_read = OFF;

I didn't say anything about new transaction isolation level.
And personally I don't like introducing non-standard isolation levels.

Antony's patch added "local-only" variables, that one could not set with SET GLOBAL. It was not sufficient, as such a protection can be always bypassed with @@init_connect variable.

Vlad, Please look into this problem. It is similar to 34486.  We need this setting to apply only to the local session, not the global.  I have found that;
SET GLOBAL FALCON_CONSISTENT_READ=OFF gets applied to the local session with its next transaction.  
SET GLOBAL FALCON_CONSISTENT_READ=[ON/OFF] should be an error.  
SET FALCON_CONSISTENT_READ=[ON/OFF] should work for the local session.  

You may need to try MYSQL_THDVAR_BOOL instead of MYSQL_SYSVAR_BOOL.  I was messing with this a couple of months ago, but it was deferred out of 6.0.4.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42844

ChangeSet@1.2821, 2008-02-22 16:21:34+01:00, vvaintroub@wva. +25 -0
  Bug#33041 : cannot set FALCON_CONSISTENT_READ for local sessions.
  
  Solution is to change the scope of the variable to be connection-specific
  such that SET FALCON_CONSISTENT_READ=<ON|OFF> modifies the setting
  only for current connection.
  
  SET GLOBAL FALCON_CONSISTENT_READ still works, but it affects only 
  current and new connections. Currently it is not possible to disable 
  "SET GLOBAL" ,as plugin API does not provide possibility to define 
  local-only scope.

Q. Why did you take the parameter out of StorageParameters.h?
A. StorageParameters.h uses MYSQL_SYSVAR_BOOL. Now falcon_consistent_read uses MYSQL_THDVAR_BOOL.

It is the first connection-specific variable in Falcon, not the global one that needs to be defined as MYSQL_THDVAR_BOOL.

+static MYSQL_THDVAR_BOOL(consistent_read, PLUGIN_VAR_OPCMDARG,
+ "Enable Consistent Read Mode for Repeatable Reads",
+ NULL, NULL,1);

And it does *not* have the same effect as MYSQL_SYSVAR_BOOL , even if I set 0x0100== PLUGIN_VAR_THDLOCAL flag for the variable, it will be lost due to logic mask in the MYSQL_SYSVAR definition ( s. MYSQL_SYSVAR_BOOL and MYSQL_THDVAR_BOOL in plugin.h)

Q. What about GLOBAL FALCON_CONSISTENT_READ?
A. SET GLOBAL FALCON_CONSISTENT_READ still works, *but* differently, i.e better. It does zero effect for existing transactions, and used with current and newly created connections only. I put together falcon_bug_33041.test to demonstrate this behavior.

As we always can get the value for current connection via THDVAR(consistent_read), there is no reason to do our own hooks for variable update and check. (I originally planned to implement check() hook to reject GLOBAL setting, but found that GLOBAL is not passed)

Q. Why take out the code in StorageInterface::falcon_init()and StorageInterface::updateConsistentRead() that differentiates the two repeatable reads?
A. Code that we had previously can't be used - it changed global variable isolation_levels and so made the parameter change visible to all current sessions immediately. THDVAR(consistent_read) is evaluated in getTransactionIsolation(), that remaps TRANSACTION_CONSISTENT_READ to TRANSACTION_WRITE_COMMITTED if necessary.

Q. How will you know if the value sent is GLOBAL or not?
A. As I said, OPT_GLOBAL is not passed down and knowing whether GLOBAL or not is *not* possible. So I do not implement any set/check hooks and rely on the plugin API magic that always returns me correct per-connection value in THDVAR(consistent_read). I also know, that startup parameter --falcon_consistent_read if used or default is in falcon_consistent_read variable.

Patch Approved

Pushed into 6.0.4-alpha

Fix is in 6.0.5-alpha.

A note has been added to the 6.0.5 changelog:

It was not possible to set the value of falcon_consistent_read within the local scope. You can now set the global value, using SET GLOBAL, but this affects only thecurrentlocal scope andall newconnections made after the global variable was set.