Uploaded image for project: 'Spring Integration'
  1. Spring Integration
  2. INT-4178

Transaction serialization exception occurs when using JdbcLockRegistry based LockRegistryLeaderInitiator on PostgreSQL

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Complete
    • Affects Version/s: 4.3.5
    • Fix Version/s: 5.0.M1, 4.3.6
    • Component/s: JDBC Support
    • Labels:
    • Environment:
      PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.2.0-5ubuntu12) 6.2.0 20161005, 64-bit

      Description

      When using JdbcLockRegistry based LockRegistryLeaderInitiator on PostgreSQL, the node that does not hold lock will eventually fail due to transaction serialization exception:

      2016-12-01 08:49:43.581 DEBUG 8916 --- [ck-leadership-0] o.s.j.d.DataSourceTransactionManager     : Creating new transaction with name [org.springframework.integration.jdbc.lock.DefaultLockRepository.acquire]: PROPAGATION_REQUIRED,ISOLATION_SERIALIZABLE,timeout_1; ''
      2016-12-01 08:49:43.582 DEBUG 8916 --- [ck-leadership-0] o.s.j.d.DataSourceTransactionManager     : Acquired Connection [[email protected] wrapping [email protected]] for JDBC transaction
      2016-12-01 08:49:43.582 DEBUG 8916 --- [ck-leadership-0] o.s.jdbc.datasource.DataSourceUtils      : Changing isolation level of JDBC Connection [[email protected] wrapping [email protected]] to 8
      2016-12-01 08:49:43.582 DEBUG 8916 --- [ck-leadership-0] o.s.j.d.DataSourceTransactionManager     : Switching JDBC Connection [[email protected] wrapping [email protected]] to manual commit
      2016-12-01 08:49:43.583 DEBUG 8916 --- [ck-leadership-0] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL update
      2016-12-01 08:49:43.583 DEBUG 8916 --- [ck-leadership-0] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [DELETE FROM INT_LOCK WHERE REGION=? AND LOCK_KEY=? AND CREATED_DATE<?]
      2016-12-01 08:49:43.583 DEBUG 8916 --- [ck-leadership-0] o.s.jdbc.core.JdbcTemplate               : SQL update affected 0 rows
      2016-12-01 08:49:43.584 DEBUG 8916 --- [ck-leadership-0] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL update
      2016-12-01 08:49:43.594 DEBUG 8916 --- [ck-leadership-0] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [UPDATE INT_LOCK SET CREATED_DATE=? WHERE REGION=? AND LOCK_KEY=? AND CLIENT_ID=?]
      2016-12-01 08:49:43.595 DEBUG 8916 --- [ck-leadership-0] o.s.jdbc.core.JdbcTemplate               : SQL update affected 0 rows
      2016-12-01 08:49:43.595 DEBUG 8916 --- [ck-leadership-0] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL update
      2016-12-01 08:49:43.595 DEBUG 8916 --- [ck-leadership-0] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [INSERT INTO INT_LOCK (REGION, LOCK_KEY, CLIENT_ID, CREATED_DATE) VALUES (?, ?, ?, ?)]
      2016-12-01 08:49:43.596 DEBUG 8916 --- [ck-leadership-0] s.j.s.SQLErrorCodeSQLExceptionTranslator : Translating SQLException with SQL state '40001', error code '0', message [ERROR: could not serialize access due to read/write dependencies among transactions
        Detail: Reason code: Canceled on identification as a pivot, during write.
        Hint: The transaction might succeed if retried.]; SQL was [INSERT INTO INT_LOCK (REGION, LOCK_KEY, CLIENT_ID, CREATED_DATE) VALUES (?, ?, ?, ?)] for task [PreparedStatementCallback]
      2016-12-01 08:49:43.600 DEBUG 8916 --- [ck-leadership-0] o.s.j.d.DataSourceTransactionManager     : Initiating transaction rollback
      2016-12-01 08:49:43.601 DEBUG 8916 --- [ck-leadership-0] o.s.j.d.DataSourceTransactionManager     : Rolling back JDBC transaction on Connection [[email protected] wrapping [email protected]]
      2016-12-01 08:49:43.601 DEBUG 8916 --- [ck-leadership-0] o.s.jdbc.datasource.DataSourceUtils      : Resetting isolation level of JDBC Connection [[email protected] wrapping [email protected]] to 2
      2016-12-01 08:49:43.601 DEBUG 8916 --- [ck-leadership-0] o.s.j.d.DataSourceTransactionManager     : Releasing JDBC Connection [[email protected] wrapping [email protected]] after transaction
      2016-12-01 08:49:43.602 DEBUG 8916 --- [ck-leadership-0] o.s.jdbc.datasource.DataSourceUtils      : Returning JDBC Connection to DataSource
      

      When this occurs, the LockRegistryLeaderInitiator's thread gets stuck and eventually parked, rendering current node unable to obtain the lock once the other node releases it.

      This is due to DefaultLockRepository#acquire being defined with SERIALIZABLE transaction isolation. As explained in this SO post, callers of such code should be able to retry the transaction. JdbcLockRegistry does this approach for TransactionTimedOutException but not for CannotSerializeTransactionException.

      The ticket's reference URL contains sample project that reproduces the problem together with detailed description.

      I've fixed this locally and will submit the PR shortly.

        Attachments

          Activity

            People

            Assignee:
            abilan Artem Bilan
            Reporter:
            vpavic Vedran Pavic
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: