COMPSCI 331 Lecture Notes: Transactions PDF

COMPSCI 331 Large-Scale Software Development Transactions Paramvir Singh TRANSACTIONS Agenda  Transactions  Database-level concurrency  Managing transactions with JPA 2 TRANSACTIONS Transaction essentials...

COMPSCI 331 Large-Scale Software Development Transactions Paramvir Singh TRANSACTIONS Agenda  Transactions  Database-level concurrency  Managing transactions with JPA 2 TRANSACTIONS Transaction essentials transfer( amount, from, to ) sumAccountBalances( ) Client Banking service Tx A Tx B Client Database managing transactions 3 TRANSACTIONS ACID attributes  Atomicity  All operations in a transaction execute as an atomic unit  Consistency  Concurrently executing transactions do not compromise the data they operate on  Isolation  The effects of concurrently executing incomplete transactions shouldn’t be visible  Durability  Changes made by a transaction should be durable, even if the system fails after the transaction has completed successfully 4 TRANSACTIONS Programming transactions with JPA EntityManagerFactory factory = … EntityManager em = factory.createEntityManager( ); try { EntityTransaction tx = em.getTransaction( ); tx.begin( ); Execute queries / make EntityManager invocations tx.setRollbackOnly( ); tx.commit(); } catch(Exception e) { Exception handling code logger.error( … ) } finally { if( em != null && em.isOpen( ) ) { em.close( ); } } 5 TRANSACTIONS Database-level concurrency control Problem #1 Lost Update Tx A 3. commit 1. update Transaction A updates data D1 ; transaction B does likewise. Transaction A commits and then transaction B aborts. D1 D1 B’s abortion returns D1 to its state when B started, undoing 2. update Tx B 4. rollback A’s update. Problem #2 Dirty Read Tx A 3. rollback 1. update Transaction A updates data D1 ; transaction B reads the updated (and uncommitted) value for D1. Transaction A D1 D1 aborts while B commits. 2. select Tx B 4. commit B may have acted incorrectly based on the state of D1 that it read. 6 TRANSACTIONS Database-level concurrency control Problem #3 Unrepeatable Read Tx A 1. select 4. select Transaction A reads data item D1 and sees a different value each time. Between the reads, transaction B has committed a D1 D1 D1 write to D1. 2. update Tx B 3. commit Problem #4 Last Commit Wins Tx A 4. commit 1. select 3. update Transactions A and B each read data item D1 and then go on to write to D1. D1 D1 D1 D1 5. update The last transaction (B) to commit its write wins, 2. select Tx B 6. commit overwriting the earlier transaction’s write. 7 TRANSACTIONS Database-level concurrency control Problem #5 Phantom Read Tx A 1. select 4. select A Phantom Read occurs where a transaction (A) executes a D1 query twice, and the second result includes data that wasn’t D1 D2 D2 visible in the first result. 2. insert Tx B 3. commit A concurrently executing transaction (B) inserts data between the other transaction’s queries. 8 TRANSACTIONS Isolation levels Problems that can occur Increasing isolation Isolation level Lost Dirty Unrepeatable Phantom updates reads reads reads Read Uncommitted No Yes Yes Yes Read Committed No No Yes Yes Repeatable Read No No No Yes Serializable No No No No Increasing performance 9 TRANSACTIONS The persistence context EntityManagerFactory#createEntityManager( ) EntityManager#close( ) Lifetime  JPA’s persistence context manages persistent objects; the context: Persistence context  Serves as a cache that enforces repeatable read semantics  Performs dirty checking of objects and syncs them with the database  Provides a guaranteed scope of object identity  If entityA == entityB then entityA.getId( ).equals( entityB.getId( ) ) SQL database 10 TRANSACTIONS Optimistic concurrency control (OCC) EntityManagerFactory factory = … EntityManager em = factory.createEntityManager( );  Optimistic concurrency control involves try { working on a local copy of data and then EntityTransaction tx = em.getTransaction( ); updating the database tx.begin( ); select * from ITEM where ID = 116  OCC uses version numbers for data Item item = em.find( Item.class, 116 );  Whenever an entity is updated, its version assertEquals( 0, item.getVersion( ) ); number is incremented item.setName( “New name” ); tx.commit( ); update ITEM } catch(Exception e) { set NAME = “New name”,VERSION = 1 logger.error( … ); where ID = 116 and VERSION = 0 @Entity public class Item { } finally { @Version if( em != null && em.isOpen( ) ) { private long version; em.close( ); … } } } 11 TRANSACTIONS OCC: Last Commit Wins → First Commit Wins id = 116 id = 116 version = 0 version = 1 Item Item Item 1: find 3. setName Tx A 5. commit ITEM ITEM ID VERSION NAME ID VERSION NAME 116 0 … 116 1 … OptimisticLockException Tx B 6. commit 2. find 4. setName Item Item id = 116 Item id = 116 version = 0 version = 1 12 TRANSACTIONS A repeatable read problem Assume a many-to-one relationship between Item and Category. CATEGORY A CATEGORY B CATEGORY C Item #1, price = $10 Item #3, price = $15 Item #5, price = $25 Item #2, price = $20 Item #4, price = $10 Sum total = $30 Sum total = $25 Sum total = $25 Sum total = $80 CATEGORY A CATEGORY B CATEGORY C Item #1, price = $10 Item #3, price = $15 Item #5, price = $25 Item #2, price = $20 Item #4, price = $10 Item #2, price = $20 Sum total = $30 Sum total = $25 Sum total = $45 Sum total = $100 Calculating the sum of all items across several categories requires that the “get items in each category” is a repeatable read. 13 TRANSACTIONS Forced version checking try { EntityTransaction tx = em.getTransaction( ); tx.begin( ); For each Category, query all Item instances using an OPTIMISTIC “lock” mode. BigDecimal sumTotal = new BigDecimal( 0 ); for( Long categoryId : categories ) { List< Item > items = em.createQuery( “select i from Item i where i.category.id = :catId” ).setLockMode( LockModeType.OPTIMISTIC ).setParameter( “catId”, categoryId ).getResultList( ); for( Item item : items ) { For each Item object loaded, the JPA implementation sumTotal = sumTotal.add( item.getBuyNowPrice( ) ); will execute a SQL select statement to compare the } Item’s version value with that of its corresponding row tx.commit( ); in the database. } … If the version values differ or if the row no longer exists, commit( ) throws an OptimisticLockException. 14  id = 1 id = 3 id = 5 Note: Item #2 is already cached in transaction version = 0 version = 0 version = 0 A’s persistence context. The category C Item Item Item query returns the cached instance. id = 2 id = 4 id = 2 Item version = 0 Item version = 0 Item version = 0 1. Query category A 5. Query category B 6. Query category C OptimisticLockException Tx A 7. commit Tx B 4. commit id = 2 2. Query Item 2 Item version = 1 3. Update Item 2 ITEMS table prior to transaction ITEMS table after transaction B has execution committed. ID VERSION CATEGORY ID VERSION CATEGORY 1 0 A 1 0 A 2 0 A 2 1 C 3 0 B 3 0 B 4 0 B 4 0 B 15 5 0 C 5 0 C TRANSACTIONS TRANSACTIONS An “invisible conflict” problem Assumptions: Two transactions are bidding on a common Item. The Item currently has 1 bid of $12. Item and Bid are entities related by a many-to-one unidirectional relationship. A Bid has a timestamp; the most recent bid should be the winning bid. 4. If this transaction’s bid ($13) is higher than the Item’s 1. Query highest bid for highest bid ($12), associate the Bid with the Item. Item → $12 Tx A 6. commit T2 Bid $13 3. If this transaction’s bid ($15) is higher than the Item’s 2. Query highest bid for highest bid ($12), associate the Bid with the Item. BIDS Item → $12 ID ITEM_ID TIMESTAMP AMOUNT Tx B 5. commit 1 116 23/08/15 10:26pm $12.00 T1 Bid $15 2 116 24/08/15 9:30am $15.00 3 116 24/08/15 9:31am $13.00 16 TRANSACTIONS Forced version increment try { On loading the Item, the JPA implementation will EntityTransaction tx = em.getTransaction( ); increment the object’s version number – even though tx.begin( ); the Item isn’t going to be modified. Item item = em.find( Item.class, 116, LockModeType.OPTIMISTIC_FORCE_INCREMENT ); This persists a new Bid, as long as the bid is higher Bid highestBid = queryHighestBid( em, item ); than the Item’s present highest bid. try { Bid newBid = new Bid( new BigDecimal( 15.00 ), item, highestBid ); em.persist( newBid ); } catch( InvalidBidException e ) { // Bid too low. At commit time, the JPA implementation executes an } SQL insert for the new Bid and an update for the Item, to update its version number. tx.commit( ); } … The update will fail if the Item’s version has since been incremented, e.g. by other transactions running this place-bid work unit. 17 TRANSACTIONS id = 116 id = 1 version = 6 amount = $12 amount = $13 Item Bid Bid 1. Query Item 116 4. Query highest Bid 5. Persist new Bid OptimisticLockException Tx A 8. commit Tx B 7. commit 2. Query Item 116 3. Query highest Bid 6. Persist new Bid update ITEMS set VERSION = 6 Item Bid Bid where ID = 116 and VERSION = 5 id = 116 id = 1 id = 2 version = 6 amount = $12 amount = $15 insert into BIDS values … Database tables prior to transaction execution Database tables after transaction B has committed ID VERSION ITEMS ID VERSION ITEMS 116 5 116 6 ID ITEM_ID TIMESTAMP AMOUNT BIDS ID ITEM_ID TIMESTAMP AMOUNT BIDS 1 116 23/08/15 10:26pm $12.00 1 116 23/08/15 10:26pm $12.00 2 116 24/08/15 9:30am $15.00 18 TRANSACTIONS Pessimistic locking try { EntityTransaction tx = em.getTransaction( ); tx.begin( ); For each Category, query all Item instances, using a database-level read-lock to lock ITEM table rows. BigDecimal sumTotal = new BigDecimal( 0 ); This guarantees repeatable reads. for( Long categoryId : categories ) { List< Item > items = em.createQuery( “select i from Item i where i.category.id = :catId” ).setLockMode( LockModeType.PESSIMISTIC_READ ).setHint( “javax.persistence.lock.timeout”, 5000 ).setParameter( “catId”, categoryId ).getResultList( ); for( Item item : items ) { If the required locks can’t be obtained within 5 sumTotal = sumTotal.add( item.getBuyNowPrice( ) ); seconds, the query throws either a } LockTimeoutException or a PessimisticLockException. tx.commit( ); } … The acquired locks are released. 19 TRANSACTIONS id = 1 id = 3 id = 5 Item Item Item id = 2 id = 4 Item Item 1. Query category A 4. Query category B 5. Query category C Tx A 6. commit Tx B BLOCKED 7. commit Row 2 of ITEMS is 2. Query Item 2 3. Update Item 2 unlocked id = 2 Row 2 of ITEMS is locked for Item reading by TxA (1) ITEMS table prior to transaction ITEMS table after transaction A has ITEMS table after transaction B has execution committed. committed. ID CATEGORY ID CATEGORY ID CATEGORY 1 A 1 A 1 A 2 A 2 A 2 C 3 B 3 B 3 B 4 B 4 B 4 B 20 5 C 5 C 5 C TRANSACTIONS Pessimistic locking try { On loading the Item, the JPA implementation will EntityTransaction tx = em.getTransaction( ); attempt to acquire a database-level write-lock on the tx.begin( ); ITEM table row – even though the loaded Item won’t be modified. Item item = em.find( Item.class, 116, LockModeType.PESSIMISTIC_WRITE ); This persists a new Bid, as long as the bid is higher Bid highestBid = queryHighestBid( em, item ); than the Item’s present highest bid. try { Bid newBid = new Bid( new BigDecimal( 15.00 ), item, highestBid ); em.persist( newBid ); } catch( InvalidBidException e ) { // Bid too low. } At commit time, the JPA implementation simply executes a SQL insert for a new valid Bid. The tx.commit( ); database-level lock is then released. } … 21  id = 1 id = 2 id = 116 amount = $12 amount = $13 Item Bid Bid 1. Query Item 116 3. Query highest Bid 4. Persist new Bid Tx A 5. commit Bid $13 Row 1 of ITEMS is unlocked Tx B BLOCKED 8. commit Bid $15 2. Query Item 116 6. Query highest Bid 7. Persist new Bid Row 1 of ITEMS is Bid Bid locked for writing by id = 1 id = 3 TxA (1) amount = $15 amount = $13 ID ITEM_ID TIMESTAMP AMOUNT BIDS 1 116 23/08/15 10:26pm $12.00 Only this row exists prior to transaction execution 2 116 24/08/15 9:30am $13.00 Row inserted when transaction A commits 3 116 24/08/15 9:31am $15.00 Row inserted when transaction B commits 22 TRANSACTIONS TRANSACTIONS A problem with locking try { EntityTransaction tx = em.getTransaction( ); tx.begin( ); Item item1 = em.find( Item.class, 116 ); item1.setName( “First new name” ); Item item2 = em.find( Item.class, 117 ); item2.setName( “Second new name” ); Bid highestBid = queryHighestBid( em, item ); Locks ITEM row 116 update ITEM set … where ID = 116; tx.commit( ); update ITEM set … where ID = 117; em.close( ); } … Locks ITEM row 117 TxA TxB update ITEM set … where ID = 116; update ITEM set … where ID = 117; update ITEM set … where ID = 117; update ITEM set … where ID = 116; 23 TRANSACTIONS Solutions to deadlock Lock whole tables and not rows.  Use the Serializable isolation level Set property hibernate.order_updates to true in persistence.xml.  Order database updates based on primary key values  Use a deadlock detection and resolution strategy Where a transaction (A) has held a lock for more than a specified time, if another transaction (B) requests the lock,  Lock timeouts transaction A is aborted – releasing the lock.  Break deadlock cycles TxA  TxB 24 TRANSACTIONS What have we learned today?  Transactions are necessary to ensure correctness in a concurrent execution environment and to enforce atomicity of work units  Relational databases provide isolation levels that trade correctness for performance; JPA assumes the Read Committed level from the database, and, through the persistence context guarantees repeatable reads for individual objects  JPA offers optimistic concurrency control through version numbers; to cater for particular transactional use cases JPA allows for forced version checking and incrementing  JPA also offers pessimistic concurrency control, utilising locks at the database level 25 TRANSACTIONS COMPSCI 331 Part-1 Feedback Please let us know about your COMPSCI 331 experience so far by filling this Google Form: https://forms.gle/h8eRAy5ypbYAjJPa7 26