Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21921

Make transaction_isolation into a system variable

Details

    Description

      In MariaDB, we have a confusing problem where:

      • The transaction_isolation option can be set in a configuration file, but it cannot be set dynamically.
      • The tx_isolation system variable can be set dynamically, but it cannot be set in a configuration file.

      Therefore, we have two different names for the same thing in different contexts. This is needlessly confusing, and it complicates the documentation.

      MySQL 5.7 solved this problem by making transaction_isolation into a system variable:

      https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_transaction_isolation

      Same has been done to transaction_read_only option:
      https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-20.html

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31746 Problems with tx_isolation after MDEV-21921

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          is part of

          Task - A task that needs to be done. MDEV-28906 MySQL 8.0 desired compatibility

          • Major - Major loss of function.
          • Open
          relates to

          New Feature - A new feature of the product, which has yet to be developed. CONJ-1087 handle mariadb-11.1+ transaction_isolation

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23520 Unknown system variable 'transaction_isolation'

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31576 Unknown system variable 'transaction_isolation'

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32109 Compatibility Broken with Mysql JDBC Connector

          • Major - Major loss of function.
          • Closed
          links to

          Web Link tx_isolation in MariaDB knowledge base

          Web Link tx_read_only in MariaDB knowledge base

          Activity

            Descending order - Click to sort in ascending order
            greenman Ian Gilfillan added a comment -

            Thanks junqi_xie for documenting the changes. Note that I reverted and will rework the SET TRANSACTION changes at https://mariadb.com/kb/en/set-transaction/+revision/125221/125173/, as the Knowledge Base is for all maintained versions, currently 10.3+, and the new instructions you specified will not work on releases prior to 11.1

            greenman Ian Gilfillan added a comment - Thanks junqi_xie for documenting the changes. Note that I reverted and will rework the SET TRANSACTION changes at https://mariadb.com/kb/en/set-transaction/+revision/125221/125173/ , as the Knowledge Base is for all maintained versions, currently 10.3+, and the new instructions you specified will not work on releases prior to 11.1
            danblack Daniel Black added a comment -

            Thank you very much Junqi Xie

            danblack Daniel Black added a comment - Thank you very much Junqi Xie
            junqi_xie Junqi Xie added a comment -

            Hi all! This is my first attempt to create a patch to MariaDB community. https://github.com/MariaDB/server/pull/2491

            To implement this feature, I mostly backport the code from MySQL 5.7 branch, noted here. It adds 2 new system variables (transaction_isolation and transaction_read_only) which references the same field as the original variables (tx_isolation and tx_read_only). The original variables are marked as deprecated, and users will get a warning when setting the original variables.

            To avoid "surprises" to mariadb developers, I kept the internal representation of these variables. In other words, the change is only visible from a user's perspective. I also updated all the variables referenced in the tests for consistency.

            @serg @nikitamalyavin What do you think about this approach and the PR? Thanks in advance.

            junqi_xie Junqi Xie added a comment - Hi all! This is my first attempt to create a patch to MariaDB community. https://github.com/MariaDB/server/pull/2491 To implement this feature, I mostly backport the code from MySQL 5.7 branch, noted here . It adds 2 new system variables ( transaction_isolation and transaction_read_only ) which references the same field as the original variables ( tx_isolation and tx_read_only ). The original variables are marked as deprecated, and users will get a warning when setting the original variables. To avoid "surprises" to mariadb developers, I kept the internal representation of these variables. In other words, the change is only visible from a user's perspective. I also updated all the variables referenced in the tests for consistency. @serg @nikitamalyavin What do you think about this approach and the PR? Thanks in advance.
            diego dupin Diego Dupin added a comment - - edited

            btw, mysql java driver set transaction_isolation when creating connection, making java connector incompatible with MariaDB:
            see https://github.com/mysql/mysql-connector-j/blob/release/8.0/src/main/user-impl/java/com/mysql/cj/jdbc/ConnectionImpl.java#L1211
            resulting in error like this one : 

            java.sql.SQLException: Unknown system variable 'transaction_isolation'
            		 
            	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
            		 
            	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
            		 
            	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:829)
            		 
            	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:449)
            		 
            	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:242)
            		 
            	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
            		 
            	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
            		 
            	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:251)
            		 
            	at test.Test.main(Test.java:15)

            mysql ODBC use it as well, but only to retrieve isolation level: https://github.com/mysql/mysql-connector-odbc/blob/7f85d3d3e59c30c754570b1a1b47df4b5737e6e2/test/my_tran.c

            diego dupin Diego Dupin added a comment - - edited btw, mysql java driver set transaction_isolation when creating connection, making java connector incompatible with MariaDB: see https://github.com/mysql/mysql-connector-j/blob/release/8.0/src/main/user-impl/java/com/mysql/cj/jdbc/ConnectionImpl.java#L1211 resulting in error like this one : java.sql.SQLException: Unknown system variable 'transaction_isolation' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java: 129 ) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java: 122 ) at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java: 829 ) at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java: 449 ) at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java: 242 ) at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java: 198 ) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java: 677 ) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java: 251 ) at test.Test.main(Test.java: 15 ) mysql ODBC use it as well, but only to retrieve isolation level: https://github.com/mysql/mysql-connector-odbc/blob/7f85d3d3e59c30c754570b1a1b47df4b5737e6e2/test/my_tran.c
            serg Sergei Golubchik added a comment -

            ralf.gebhardt@mariadb.com, no, I would consider it backward compatibility

            serg Sergei Golubchik added a comment - ralf.gebhardt@mariadb.com , no, I would consider it backward compatibility
            serg Sergei Golubchik added a comment -

            tx_isolation is a strange name, I agree with GeoffMontee, better to create @@transaction_isolation sysvar, like MySQL did.

            We can mark tx_isolation deprecated and remove it eventually.

            serg Sergei Golubchik added a comment - tx_isolation is a strange name, I agree with GeoffMontee , better to create @@transaction_isolation sysvar, like MySQL did. We can mark tx_isolation deprecated and remove it eventually.

            People

              danblack Daniel Black
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              3 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.