Skip to content
  • Jon Olav Hauglid's avatar
    c63e26d6
    WL#5968: Implement START TRANSACTION READ (WRITE|ONLY); · c63e26d6
    Jon Olav Hauglid authored
      
    This worklog implements support for explicitly or implicitly
    starting read-only transactions. During a read-only transaction
    all DDL statements as well as inserts, updates and deletions of
    data in non-temporary tables will return an error message:
    "Cannot execute statement in a READ ONLY transaction."
    
    The reason for implementing support for read-only transactions
    is that it allows engine optimizations that will result in
    performance improvements for read-only workloads.
    
    Read-only transactions can be explicitly started using the
    new START TRANSACTION READ ONLY syntax. Similarly, read-write
    transactions can be explicitly started using START TRANSACTION
    READ WRITE.
    
    Read-only transactions can be implicitly started by first changing
    the default access mode to read-only using:
    *) SET [GLOBAL|SESSION] TRANSACTION READ ONLY
       (READ WRITE is also supported)
    *) Global/session dynamic server variable "tx_read_only".
       This has the same effect as SET GLOBAL/SESSION TRANSACTION.
    *) Server startup option --transaction-read-only
    and then starting a transaction without explicit access mode.
    Note that read-write is the default access mode (matching
    existing behavior before this worklog).
    
    Note that SET TRANSACTION READ ONLY (or READ WRITE) sets the
    access mode for the next (and only next) transaction, while
    SET SESSION sets the session server variable which will
    affect all following transactions.
    
    The worklog also updates the client/server protocol by adding
    a new SERVER_STATUS_IN_TRANS_READONLY server status flag.
    This allows clients to examine the access mode of any current
    transaction.
    
    Incompatible change: SET TRANSACTION is now a separate
    statement from SET. This means that it is now longer possible
    to execute e.g. SET @a= 1, TRANSACTION ISOLATION LEVEL SERIALIZABLE.
    c63e26d6
    WL#5968: Implement START TRANSACTION READ (WRITE|ONLY);
    Jon Olav Hauglid authored
      
    This worklog implements support for explicitly or implicitly
    starting read-only transactions. During a read-only transaction
    all DDL statements as well as inserts, updates and deletions of
    data in non-temporary tables will return an error message:
    "Cannot execute statement in a READ ONLY transaction."
    
    The reason for implementing support for read-only transactions
    is that it allows engine optimizations that will result in
    performance improvements for read-only workloads.
    
    Read-only transactions can be explicitly started using the
    new START TRANSACTION READ ONLY syntax. Similarly, read-write
    transactions can be explicitly started using START TRANSACTION
    READ WRITE.
    
    Read-only transactions can be implicitly started by first changing
    the default access mode to read-only using:
    *) SET [GLOBAL|SESSION] TRANSACTION READ ONLY
       (READ WRITE is also supported)
    *) Global/session dynamic server variable "tx_read_only".
       This has the same effect as SET GLOBAL/SESSION TRANSACTION.
    *) Server startup option --transaction-read-only
    and then starting a transaction without explicit access mode.
    Note that read-write is the default access mode (matching
    existing behavior before this worklog).
    
    Note that SET TRANSACTION READ ONLY (or READ WRITE) sets the
    access mode for the next (and only next) transaction, while
    SET SESSION sets the session server variable which will
    affect all following transactions.
    
    The worklog also updates the client/server protocol by adding
    a new SERVER_STATUS_IN_TRANS_READONLY server status flag.
    This allows clients to examine the access mode of any current
    transaction.
    
    Incompatible change: SET TRANSACTION is now a separate
    statement from SET. This means that it is now longer possible
    to execute e.g. SET @a= 1, TRANSACTION ISOLATION LEVEL SERIALIZABLE.
Loading