...

References:

• Jira Legacy
  server System Jira serverId 4733707d-2057-3a0f-ae5e-4fd8aff50176 key CPS-899

• CPS Internal Relation DB Schema
• https://www.javatpoint.com/hql
• https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html
• https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED
• https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT
• https://docstore.mik.ua/orelly/java-ent/servlet/ch09_04.htm

Sessions

• Short life session
  
  • LazyInitialization exception
  • write changes to DB every step
• Open-Session-in-View
• Extended session
  • session is never closed
  • manual session.flush (send all open statements to DB)
  • session stay in persistent state

Hibernate's Session

• Session allows physical connection with a database
  • only creates connection when required
• openSession()/getCurrentSession()
• session.close()
• Session cannot be kept as it is not threadsafe
• Perform all crud operations (read, update and delete operations)
• HibernateContentQueryFactory
  • can use a provided EntityManager object to create a query
• Object states
  • Transient - when object unattached to any session
  • Persistent
    • attach object to a session with 'persist' or 'save' method
  • Detached
    • object is not managed by any session
      • session.close();

Transactions

Transactions allows for grouping of operations

• All SQL statements execute inside a transaction
• transaction is either completely committed or rolled back

Transaction isolation levels

• All JDBC connection initialises with the default isolation level of the DBMS used but can be configured

Which isolation level?

• Read uncomitted isolation
  • allows a transaction to read uncomitted data (dirty read) but not lost updates
  • can be implemented with write locks
• Read committed isolation
  • transaction executes two nonrepeatble reads but not dirty reads
  • can be implemented with shared read locks and write locks
    • read transactions does not restrict other transactions from reading
    • uncomitted update/write transaction restricts other transactions from accessing the row
• Repeatable read isolation
  • allows neither unrepeatable reads nor dirty reads
    • read transactions does not restrict other transactions from reading but restricts from updating/writing
    • write transaction blocks all
• Serializable isolation

Plain JDBC Transaction

• java.sql.Connection
• transaction is tarted with setAutoCommit(false) on aJDBC connection 
• transaction is ended with commit()
• immediate rollback - rollback() 
• Isolation levels
  • e.g. connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
• Save point (Nested transactions in spring
  • e.g. connection.setSavepoint();

Hibernate

• JDBC Connection for every session
• Transaction interface is customizable to implement own TransactionFactory
• beginTransaction() call on session instance
  • equivalent to setAutoCommit(false) on plain JDBC transaction
  • Session is bound to the transaction
  • after commit or rollback connection is released
  • closing session detaches all other resource/all managed persistent instances
• rollback()
• any hibernate operation can throw a RunTimeException
• setTimeout() - sets the time in seconds for a transaction is allowed to run
• Isolation level can be configured and all connections and transactions will be affected
  • e.g. hibernate.connection.isolation = 4
• lock()
  • can be set with query statement
    • .setLockOptions() 
  • LockMode.UPGRADE
    • sets a lock on the row(s) that represents the Entity instance
    • e.g. session.buildLockRequest(LockOptions.UPGRADE).lock(entityName)
  • LockMode.READ
  • LockMode.UPGRADE_NOWAIT
    • disables waiting for concurrent lock releases, throws exception when it cannot get a lock
    • automatically falls to LockMode.UPGRADE if DB does not support NO_WAIT
  • LockMode.FORCE
  • LockMode.WRITE

Spring/Spring Boot Transaction Management

Annotation '@Transactional'

• Spring '@Transactional' annotation handles the following transactions based on raw JDBC transaction

  Code Block
  Connection connection = dataSource.getConnection(); try (connection) { connection.setAutoCommit(false); // execute some SQL that e.g. // inserts the user into the db and retrieves the autogenerated id // userDao.save(user); connection.commit(); } catch (SQLException e) { connection.rollback(); }

  
  

• We can use this annotation at either a class or method level
• requires specification of transaction manager in spring configuration
  • '@Configuration'
  • '@EnableTransactionManagement'
• Supports the following configurations
  • Propagation type
    • '@Transactional(propagation = Propagation.REQUIRED)'
      • method opens a new transaction using the same existing connection
    • '@Transactional(propagation = Propagation.REQUIRES_NEW)'
      • opens new transaction in a new connection
    • '@Transactional(propagation = Propagation.MANDATORY)
      • does not open a new transaction, requires a pre-existing transaction to invoke method or else Spring throws an exception
  •  Isolation level
    • '@Transactional(isolation = Isolation.SERIALIZABLE)'
    • Postgres default isolation level is 'READ COMMITTED'
  • Timeout
  • readOnly flag
  • Rollback rules
    • 2 ways to rollback transaction
      • declarative
        • '@Transactional(rollbackFor = { SQLException.class })'
      • programmatic
        • uses 'TransactionAspectSupport'

Spring and Hibernate Transaction Management

Allows syncing of Spring's '@Transactional' and Hibernate or JPA

• HibernateTransactionManager
  • Ensures management of transaction via Hibernate (SessionFactory)
    • creates a Session instance
    • opens a transaction (setAutocommit(false))
    • invokes transactional methods
    • commits transaction
    • close session/jdbc connection
  • Allows Spring code to use same transaction in non-hibernate
    • can use HQL or jdbc template

Postgresql lock

BEGIN;
SET LOCAL lock_timeout = '4s';
<SELECT FOR UPDATE>
<UPDATE>
COMMIT;

• SELECT FOR UPDATE
  • locks rows from another transaction that attempts to lock
• SET LOCAL lock_timeout = '4s';
• Pessimistic lock with skip locked
  • i.e. "select * from sampleTable where used=false for update skip locked limit 1" 
    • skip the one that's locked and move on

Spring data '@lock' annotation

• method level
  • sets the lock mode type for the target query
    • pesssimistic lock modes
      • pessimistic_read
      • pessimistic_write
      • pessismitic_force_increment
      • pessimistic_write
  • timeouts
    • use of '@QueryHints'
• can be used on overwritten CrudRepository methods