Each database application is unique. While most of the time, deleting a record is the best approach, there are times when the application requirements demand that database records should never be physically deleted.
So who uses this technique?
For instance, StackOverflow does it for all Posts (e.g. Questions and Answers). The StackOverflow
Posts table has a
ClosedDate column which acts as a soft delete mechanism since it hides an Answer for all users who have less than 10k reputation.
If you’re using Oracle, you can take advantage of its Flashback capabilities, so you don’t need to change your application code to offer such a functionality. Another option is to use the SQL Server Temporal Table feature.
However, not all relational database systems support Flashback queries, or they allow you to recover a certain record without having to restore from a database backup. In this case, Hibernate allows you to simplify the implementation of soft deletes, and this article is gong to explain the best way to implement the logical deletion mechanism.
Continue reading “The best way to soft delete with Hibernate”
In Concurrency Control theory, there are two ways you can deal with conflicts:
- You can avoid them, by employing a pessimistic locking mechanism (e.g. Read/Write locks, Two-Phase Locking)
- You can allow conflicts to occur, but you need to detect them using an optimistic locking mechanism (e.g. logical clock, MVCC)
Because MVCC (Multi-Version Concurrency Control) is such a prevalent Concurrency Control technique (not only in relational database systems, in this article, I’m going to explain how it works.
Continue reading “How does MVCC (Multi-Version Concurrency Control) work”
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.
Continue reading “How does a relational database work”
As previously explained, you can run database integration tests 20 times faster! The trick is to map the data directory in memory, and my previous article showed you what changes you need to do when you have a PostgreSQL or MySQL instance on your machine.
In this post, I’m going to expand the original idea, and show you how you can achieve the same goal using Docker and tmpfs.
Continue reading “How to run integration tests at warp speed using Docker and tmpfs”
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).
Continue reading “How does database pessimistic locking interact with INSERT, UPDATE, and DELETE SQL statements”