Uploaded byConstantine Slisenka
Database transaction isolation and locking in Java
The document discusses transaction isolation and locking mechanisms in Java, focusing on various database transaction phenomena such as dirty reads, non-repeatable reads, and phantom inserts. It explains the differences between optimistic and pessimistic locking strategies, detailing how different databases implement these concepts and their respective isolation levels. Additionally, it covers best practices for managing database transactions to avoid issues like deadlocks and performance degradation.
More Related Content
![[135] 오픈소스 데이터베이스, 은행 서비스에 첫발을 내밀다.](https://cdn.slidesharecdn.com/ss_thumbnails/35-171016061446-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
Similar to Database transaction isolation and locking in Java
Database transaction isolation and locking in Java
- 1. 1CONFIDENTIAL Database Transaction Isolation andLocking in Java Kanstantsin Slisenka, Senior Software Engineer EPAM Systems Aug 25, 2016
- 2. 2CONFIDENTIAL About me EXPERIENCE INTERESTED IN •Java backend developer • Oracle Certified Java 7 Programmer, OCEWCD, OCEJPAD • Speaker at Java tech talks • Java backend, SOA, databases, integration frameworks • Solution architecture • High load, fault-tolerant, distributed, scalable systems kanstantsin_slisenka@epam.com github.com/kslisenko www.slisenko.net Kanstantsin Slisenka
- 3. 3CONFIDENTIAL DID YOU USETX ISOLATION OR LOCKING AT YOUR PROJECTS?
- 4. 4CONFIDENTIAL •Transaction phenomena andisolation levels •Pessimistic and optimistic approaches •Transaction isolation in MySQL •Database-level locks in MySQL •JPA features for locking Agenda
- 5. 5CONFIDENTIAL WHAT IS DATABASETRANSACTION?
- 6. 6CONFIDENTIAL Database transactions andACID Atomicity1 Consistency2 Isolation3 Durability4
- 7. 7CONFIDENTIAL • Problem ofconcurrent updates made by parallel transactions • No problem if no concurrent updates • Databases have protection Transaction phenomena PROBLEM PHENOMENA • Dirty read • Non-repeatable read • Phantom insert
- 8. 8CONFIDENTIAL Transaction phenomena: dirtyread Transaction 1 Transaction 2customer balance tom 1000 time begin begin DATABASES ARE PROTECTED AGAINST THIS IN REAL LIFE ~ not committed data • Transactions can read not committed (dirty) data of each other • Other transaction rollbacks, decision was made based on never existed data PROBLEM
- 9. 9CONFIDENTIAL • Transactions canread not committed (dirty) data of each other • Other transaction rollbacks, decision was made based on never existed data Transaction phenomena: dirty read PROBLEM Transaction 1 Transaction 2customer balance tom 1000 time begin begin balance = 1100 customer balance tom ~1100~ ~ not committed data DATABASES ARE PROTECTED AGAINST THIS IN REAL LIFE
- 10. 10CONFIDENTIAL • Transactions canread not committed (dirty) data of each other • Other transaction rollbacks, decision was made based on never existed data Transaction phenomena: dirty read PROBLEM Transaction 1 Transaction 2customer balance tom 1000 time begin begin balance = 1100 read not committed balance = 1100 customer balance tom ~1100~ customer balance tom ~1100~ ~ not committed data DATABASES ARE PROTECTED AGAINST THIS IN REAL LIFE
- 11. 11CONFIDENTIAL • Transactions canread not committed (dirty) data of each other • Other transaction rollbacks, decision was made based on never existed data Transaction phenomena: dirty read PROBLEM Transaction 1 Transaction 2customer balance tom 1000 time begin begin balance = 1100 read not committed balance = 1100 rollback customer balance tom ~1100~ customer balance tom 1000 customer balance tom ~1100~ ~ not committed data DATABASES ARE PROTECTED AGAINST THIS IN REAL LIFE
- 12. 12CONFIDENTIAL • One transactionupdates data • Other transaction reads data several times and get different results Transaction phenomena: non-repeatable read PROBLEM Transaction 1 Transaction 2customer balance tom 1000 begin begin WHEN WE CAN LIVE WITH THIS • We are fine with not the most recent data ~ not committed data time
- 13. 13CONFIDENTIAL Transaction phenomena: non-repeatableread Transaction 1 Transaction 2customer balance tom 1000 time begin begin read balance = 1000 customer balance tom 1000 ~ not committed data • One transaction updates data • Other transaction reads data several times and get different results PROBLEM WHEN WE CAN LIVE WITH THIS • We are fine with not the most recent data
- 14. 14CONFIDENTIAL Transaction phenomena: non-repeatableread Transaction 1 Transaction 2customer balance tom 1000 time begin begin balance = 900 commit read balance = 1000 customer balance tom 900 customer balance tom 1000 ~ not committed data • One transaction updates data • Other transaction reads data several times and get different results PROBLEM WHEN WE CAN LIVE WITH THIS • We are fine with not the most recent data
- 15. 15CONFIDENTIAL Transaction phenomena: non-repeatableread Transaction 1 Transaction 2customer balance tom 1000 time begin begin read balance = 1000 customer balance tom 900 customer balance tom 1000 ~ not committed data customer balance tom 900 read balance = 900 balance = 900 commit • One transaction updates data • Other transaction reads data several times and get different results PROBLEM WHEN WE CAN LIVE WITH THIS • We are fine with not the most recent data
- 16. 16CONFIDENTIAL • One transactioninserts/deletes rows • Other transaction reads several times and get different number of rows Transaction phenomena: phantom PROBLEM Transaction 1 Transaction 2customer balance tom 1000 time begin begin WHEN WE CAN LIVE WITH THIS • Read single rows, not ranges • We are fine with not the most recent data ~ not committed data
- 17. 17CONFIDENTIAL Transaction phenomena: phantom Transaction1 Transaction 2customer balance tom 1000 time begin begin get all customers where balance < 2000 got 1 record customer balance tom 1000 ~ not committed data • One transaction inserts/deletes rows • Other transaction reads several times and get different number of rows PROBLEM WHEN WE CAN LIVE WITH THIS • Read single rows, not ranges • We are fine with not the most recent data
- 18. 18CONFIDENTIAL Transaction phenomena: phantom Transaction1 Transaction 2customer balance tom 1000 time begin begin get all customers where balance < 2000 got 1 record customer balance tom 1000 jerry 500 customer balance tom 1000 insert new customer commit ~ not committed data • One transaction inserts/deletes rows • Other transaction reads several times and get different number of rows PROBLEM WHEN WE CAN LIVE WITH THIS • Read single rows, not ranges • We are fine with not the most recent data
- 19. 19CONFIDENTIAL Transaction phenomena: phantom Transaction1 Transaction 2customer balance tom 1000 time begin begin get all customers where balance < 2000 got 1 record customer balance tom 1000 jerry 500 customer balance tom 1000 insert new customer commit customer balance tom 1000 jerry 500 get all customers where balance < 2000 got 2 records ~ not committed data • One transaction inserts/deletes rows • Other transaction reads several times and get different number of rows PROBLEM WHEN WE CAN LIVE WITH THIS • Read single rows, not ranges • We are fine with not the most recent data
- 20.
- 21. 21CONFIDENTIAL Transaction isolation levels(standard) • ISO/IEC 9075:1992 • Information technology -- Database languages -- SQL http://www.contrib.andrew.cmu.edu/~shadow/s ql/sql1992.txt READ UNCOMMITED READ COMMITED REPEATABLE READ SERIALIZABLE Dirty read YES NO NO NO Non- repeatable read YES YES NO NO Phantom YES YES YES NO • Defined in SQL92 • Trade-off between performance, scalability and data protection • Same work performed with the same inputs may result in different answers, depending on isolation level • Implementation can be VERY DIFFERNT in different databases
- 22. 22CONFIDENTIAL HOW DOES ITWORK? IN DIFFERENT DATABASES
- 23. 23CONFIDENTIAL • Locking rowsor ranges • Like ReadWriteLock/synchronized in Java • Concurrent transactions wait until lock is released Optimistic and pessimistic approaches PESSIMISTIC OPTIMISTIC • Multi version concurrency control (MVCC) • Doesn’t lock anything • Save all versions of data • We work with data snapshots • Like GIT/SVN
- 24. 24CONFIDENTIAL • Shared lock– read lock, many owners • Exclusive lock – write lock, one owner Pessimistic locking BY OWNERSHIP range lock row lock user balance tom 1000 jerry 1500 EXCLUSIVE T1 SHARED EXCLUSIVE SHARED EXCLUSIVE SHARED • Row - specific rows by index (if index exists) • Range – all records by condition range lock row lock user balance tom 1000 jerry 1500 EXCLUSIVE T1 SHARED EXCLUSIVE SHARED EXCLUSIVE SHARED range lock row lock user balance tom 1000 jerry 1500 EXCLUSIVET1 SHARED EXCLUSIVE SHARED EXCLUSIVE SHARED BY SCOPE T2 T2 range lock row lock user balance tom 1000 jerry 1500 EXCLUSIVE T1 SHARED EXCLUSIVE SHARED EXCLUSIVE SHARED T2T3 T2 T3
- 25. 25CONFIDENTIAL Optimistic multi versionconcurrency control (MVCC) Updated user balance Deleted 0 tom 1000 1 tom 1000 1100 2 Transaction 1 (TS=0) READ Transaction 2 (TS=1) WRITE • Transactions see the rows with version less or equal to transaction start time • On update: new version is added • On remove: deleted column is set of new version number HOW IT WORKS Transaction 3 (TS=2) DELETE
- 26. 26CONFIDENTIAL Optimistic MVCC vspessimistic locks MVCC (OPTIMISTIC) LOCKS (PESSIMISTIC) Behavior 1. Each transaction works with it’s own version 2. Concurrent transaction fails 1. Transaction which owns lock works with data 2. Concurrent transactions wait Locks NO YES Performance and scalability GOOD BAD Deadlocks NO POSSIBLE Guarantee of recent data version NO YES Extra disk space needed YES NO Durability better (because of saved versions)
- 27. 27CONFIDENTIAL DB CONCEPT READ UNCOMMITED READ COMMITEDREPEATABLE READ SERIALIZABLE SPECIFICS Oracle MVCC NOT SUPPORTED DEFAULT return new snapshot each read NOT SUPPORTED returns snapshot of data at beginning of transaction + READ ONLY LEVEL transaction only sees data at the moment of start, writes not allowed always returns snapshots, transaction fail when concurrent update MySQL (InnoDB) MVCC return new snapshot each time DEFAULT - save snapshot at first read - return it for next reads - locks ranges - transaction lifetime - Shared lock on select MSSQL LOCKS + Double read phenomena: able to read same row twice while it is migrating to another place on disk SNAPSHOT (optimistic) - locks rows - transaction lifetime - locks ranges - transaction lifetime - selects: shared range lock - updates: exclusive lock + SNAPSHOT LEVEL - save snapshot at first read - return it for next reads - transactions fail in case of optimistic lock concurrent update return new snapshot each time LOCK (pessimistic) DEFAULT - locks rows - statements lifetime PostgreSQL MVCC NOT SUPPORTED DEFAULT return new snapshot each read - save snapshot at first read - return it for next reads predicate locking (optimistic) always returns snapshots, transaction fail when concurrent update Transaction isolation levels in different databases
- 28.
- 29. 29CONFIDENTIAL LOCK SPECIFIC OBJECTS! WANTTO OPTIMIZE?
- 30. 30CONFIDENTIAL • SELECT …LOCK IN SHARE MODE – shared (read) lock • SELECT … FOR UPDATE – exclusive (write) lock Pessimistic locking of specific rows/ranges (MySQL) LOCKING SELECTS IDEA • Increase isolation level for specific rows/ranges • Other rows/ranges can have lower isolation level
- 31. 31CONFIDENTIAL LIVE DEMO MYSQL PESSIMISTICLOCKING OF SPECIFIC OBJECTS
- 32. 32CONFIDENTIAL Database deadlocks row locksuser balance tom 1000 jerry 1500 Transaction 1 EXCLUSIVE SHAREDEXCLUSIVE SHARED Transaction 2 Database deadlocks happen because of bad application architecture design • Take locks in same order in every transaction • Use as small as possible transactions HOW TO PREVENT DEADLOCKS
- 33.
- 34. 34CONFIDENTIAL hibernate.connection.isolation=level Transaction isolation: configurationin Java JDBC Hibernate Connection c = DriverManager.getConnection("jdbc:mysql://localhost/testdb?user=test&password=pass"); c.setTransactionIsolation(level); Connection.TRANSACTION_NONE 0 Connection.TRANSACTION_READ_UNCOMMITED 1 Connection.TRANSACTION_READ_COMMITED 2 Connection.TRANSACTION_REPEATABLE_READ 4 Connection.TRANSACTION_SERIALIZABLE 8
- 35. 35CONFIDENTIAL JPA features forlocking Enum LockModeType PESSIMISTIC_READ Shared lock PESSIMISTIC_WRITE Exclusive lock EntityManager lock(Object entity, LockModeType lockMode) Makes additional locking select query just to lock entity find(Class<T> entityClass, Object primaryKey, LockModeType lockMode) Makes locking select when reading entity refresh(Object entity, LockModeType lockMode) Makes locking select when reloading entity NamedQuery @NamedQuery(name=“myQuery”, query=“…”, lockMode=LockModeType.PESSIMISTIC_READ) Allows to specify that we need locking select to any query • Complex to manually lock entity relationships • @NamedQuery is only way to specify query lock ADVANTAGES • It is really simple • Database specific things are hidden from developer • Supports parent-child entities DRAWBACKS
- 36. 36CONFIDENTIAL Transaction isolation andlocking with JPA • Repeatable reads because of EntityManager’s cache • Requests do not always go to database Behavior = EntityManager + 2’nd lvl cache + database Database user balance tom 1000 jerry 1500 2’nd level cache user balance tom 900 EntityManager 1’st level cache user balance tom ~1100~CLIENT APPLICATION
- 37. 37CONFIDENTIAL Conclusion • Do youhave problems because of concurrent updates? – Same as concurrent programming in Java – Sometimes we can allow phenomena • Transaction Isolation is trade-off between data protection and performance • Two main approaches in databases implementation: – Optimistic: no locks, data is versioned – Pessimistic: range and low locks • JPA – Simplifies usage of pessimistic locking – Adds own specific behavior because of caches • For better performance: – Prefer smaller transactions: they hold long time locks and can make deadlocks – Be careful with declarative transaction management it can make heavy transactions
- 38. 38CONFIDENTIAL Oracle Chapter 2-4 References MySQL Chapter 1 JPA Chapter12, Locking MSSQL Chapter 13 PostgreSQL Chapter 10 Examples https://github.com/ kslisenko/tx-isolation
- 39.