Databases Core Concepts

Last updated on May 6, 2025

3 min read

Atomicity - A transaction is all or nothing. It must either complete fully or not happen at all. There are no partial or intermediate results.
Consistency - The database must remain in a valid state before and after the transaction. Any transaction must transform the database from one consistent state to another.
Isolation - Concurrent transactions must not interfere with each other. Each transaction should execute as if it is the only one running, ensuring that intermediate states are not visible to other transactions.
Durability - Once a transaction is committed, its changes are permanent. These changes will survive system failures and can be recovered even if a crash occurs immediately afterward.

Optimistic Locking is a strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn’t changed before you write the record back. When you write the record back you filter the update on the version to make sure it’s atomic. (i.e. hasn’t been updated between when you check the version and write the record to the disk) and update the version in one hit.
If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.
This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session. In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next.
Example: Google sheets app where multiple users can edit a single document at once.
Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks. To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection.

Importance of SQL Tuning
- Improve response time of the queries thereby improving application performance
- Reduce server load
- Optimize resource utilization
Techniques for SQL Tuning
- Indexing - properly indexing columns can significantly improve the query times by allowing the DB to find data quickly instead of going through every record.
- Updating queries - this can involve removing parts of query, subquery, eliminating unwanted conditions or columns which are of no use. Using the right kind of join type etc.
- Query Execution plans - using this to optimize the queries by identifying bottlenecks. Looking at cost estimations, index utilization, etc
- Parametrized queries and Stored Procedures - Improves performance by reusing same execution plan, instead of creating a execution plan every time for each query. Also, these improve the security by preventing SQL injection attacks, since inputs are treated as parameters and not as executable code.
- Caching - Frequently run queries can be cached to improve response times.
- Materialized Views - These views store the physical result of the view. So, materialized view can store the response of a complex query, which can then be queried for quicker response.