How does a relational database work
Introduction While doing my High-Performance Java Persistence training, I came to realize that it’s worth explaining how a relational database works, as otherwise, it is very difficult to grasp many transaction-related concepts like atomicity, durability, and checkpoints. In this post, I’m going to give a high-level explanation of how a relational database works internally while also hinting some database-specific implementation details.
How does database pessimistic locking interact with INSERT, UPDATE, and DELETE SQL statements
Introduction Relational database systems employ various Concurrency Control mechanisms to provide transactions with ACID property guarantees. While isolation levels are one way of choosing a given Concurrency Control mechanism, you can also use explicit locking whenever you want a finer-grained control to prevent data integrity issues. As previously explained, there are two types of explicit locking mechanisms: pessimistic (physical) and optimistic (logical). In this post, I’m going to explain how explicit pessimistic locking interacts with non-query DML statements (e.g. insert, update, and delete).
A beginner’s guide to the Write Skew anomaly, and how it differs between 2PL and MVCC
Introduction Unlike SQL Server which, by default, relies on the 2PL (Two-Phase Locking) to implement the SQL standard isolation levels, Oracle, PostgreSQL, and MySQL InnoDB engine use MVCC (Multi-Version Concurrency Control), so handling the Write Skew anomaly can differ from one database to the other. However, providing a truly Serializable isolation level on top of MVCC is really difficult, and, in this post, I’ll demonstrate that it’s very difficult to prevent the Write Skew anomaly without resorting to pessimistic locking.
How does MySQL result set streaming perform vs fetching the whole JDBC ResultSet at once
Introduction I read a very interesting article by Krešimir Nesek regarding MySQL result set streaming when it comes to reducing memory usage. Mark Paluch, from Spring Data, asked if we could turn the MySQL result set streaming by default whenever we are using Query#stream or Query#scroll. That being said, the HHH-11260 issue was created, and I started working on it. During Peer Review, Steve Ebersole (Hibernate ORM team leader) and Sanne Grinovero (Hibernate Search Team Leader) expressed their concerns regarding making such a change. First of all, the MySQL result set streaming… Read More
The best way to detect database connection leaks
Introduction Database connections are not free, and that’s the reason for using a connection pooling solution in the first place. However, the connection pool alone does not solve every issue associated to managing database connections. The application developer must make sure that every Connection is closed when no longer needed. Behind the scenes, the connection pool gives a logical transaction which, when being closed, it returns back to the pool so that it can be further reused by other concurrent transactions. A connection leak happens when a connection is acquired without ever… Read More
14 High-Performance Java Persistence Tips
Introduction In this article, I’m going to show you various high-performance Java Persistence optimization tips that will help you get the most out of your data access layer. A high-performance data access layer requires a lot of knowledge about database internals, JDBC, JPA, Hibernate, and this post summarizes some of the most important techniques you can use to optimize your enterprise application. 1. SQL statement logging If you’re using a framework that generates statements on your behalf, you should always validate the effectiveness and efficiency of each executed statement. A testing-time assertion… Read More
High-Performance Java Persistence – Part Two
Another milestone has been reached The second part of High-Performance Java Persistence book is out. It’s been almost a year since I started writing this book, and seven months since I published the first part.
High-Performance Java Persistence – Chapter 16 – Caching
Part 2, Chapter 16 Every new chapter of my book is released right after it’s being completed, so the reader doesn’t have to wait for the whole part to be finished to get access to new material. Table of content This chapter explains how enterprise caching works, from database internal buffers, to application-level caching, and the second-level cache offered by Hibernate.
MySQL metadata locking and database transaction ending
Introduction As previously explained, every SQL statement must be executed in the context of a database transaction. For modifying statements (e.g. INSERT, UPDATE, DELETE), row-level locks must be taken to ensure recoverability and avoid the data anomalies. Next, I’ll demonstrate what can happen when a database transaction is not properly ended.
High-Performance Java Persistence – Chapter 12 – Inheritance
Part 2, Chapter 12 Every new chapter of my book is released right after it’s being completed, so the reader doesn’t have to wait for the whole part to be finished to get access to new material. Table of content This chapter explains JPA inheritance from a data access performance point of view.
