MySQL Bugs: #33041: Cannot set FALCON_CONSISTENT

Bug #33041	Cannot set FALCON_CONSISTENT_READ for local session.
Submitted:	6 Dec 2007 18:23	Modified:	1 Oct 18:37
Reporter:	Kevin Lewis
Status:	Closed
Category:	Server: Falcon	Severity:	S3 (Non-critical)
Version:	6.0.4	OS:	Any
Assigned to:	Vladislav Vaintroub	Target Version:	6.0.5
Triage:	D3 (Medium) / R3 (Medium) / E3 (Medium)

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.