Using Database Locking Techniques for Financial Transaction Integrity

In Chapter 1, Recognizing the Importance of Data Integrity in Finance, we discussed how locking techniques such as mutual exclusion locks can help maintain the integrity of transactions and financial data. These locking techniques (as well as the database constraints discussed in Chapter 2, Avoiding Common Data Integrity Issues and Challenges in Finance Teams) help ensure that financial numbers and transaction values add up, even if simultaneous actions or operations are happening at the same time. In this chapter, we’ll build on top of what we learned in the previous chapters and dive deeper into how race conditions can affect financial transaction integrity in databases and how these race conditions can be handled properly. This chapter will offer practical guidance on using specific SQL and database techniques to prevent transaction data integrity inconsistencies and issues.

Here are the topics that...

Technical requirements

Before we dive into our discussion, we must have the following in place:

• A web browser (to download the PostgreSQL installer)
• PostgreSQL version 15 or later (If you haven’t installed this yet on your machine, don’t worry—we will cover the step-by-step installation in this chapter)

The PostgreSQL queries are saved in the Packt GitHub repository for this book and can be accessed at https://github.com/PacktPublishing/Managing-Data-Integrity-for-Finance/tree/main/ch08.

Once these are ready, you may proceed with the next steps.

Getting started with SQL

In Chapter 5, Using Business Intelligence Tools to Fix Data Integrity Issues, we worked with spreadsheets to manage and organize data. Spreadsheets can be used to perform various calculations, data processing, and basic data storage operations. However, there are other ways to store and manage information, especially when the data grows in volume. For one, we can make use of databases, which are specialized systems designed to store, retrieve, and manage vast amounts of structured data. They offer a structured framework with clear linkages between data tables, facilitating more effective data management and querying.

Structured Query Language (SQL) is a programming language that’s used to manage relational databases. Two of the most popular types of relational databases are MySQL and PostgreSQL. MySQL, which was developed by Oracle, is the most widely used relational database management system. In this chapter, we will be using PostgreSQL, which is...

Learning how race conditions impact the transaction integrity of financial systems

Earlier in this chapter, we mentioned that PostgreSQL transactions have the ACID properties of atomicity, consistency, isolation, and durability. These qualities ensure that transactions are handled consistently and dependably. Let’s take a moment to discuss these in detail:

• Atomicity: A database is said to be atomic when it needs the entire set of transactions to succeed for the changes to be reflected in the database. If one of the smaller operations fails, the entire operation will fail. But if all the tasks succeed, the entire operation will succeed and be reflected in the database.
• Consistency: A database is consistent when the data adheres to the rules and relationships established in the database. Only changes that keep the data in a valid state should be made during a transaction. If the transaction is successful, it changes the database’s valid state to another. If...

Reviewing how database locks prevent financial transaction integrity issues

Database locks are essential for maintaining the integrity of financial transactions. They play a critical role in preventing conflicts and ensuring that concurrent access to data remains consistent. By controlling the simultaneous modification of shared resources, such as records or tables, locks ensure that transactions have exclusive access to the records they need (and ensure that the numbers add up correctly!). This helps maintain the accuracy and reliability of financial data by preventing data inconsistencies and conflicts.

Let’s go back to the table we created earlier, as shown in Figure 8.26. For instance, we need to update the price of the ticket for Conference X and add $10. A price increase of $10 is added depending on the demand:

Figure 8.26 – Current ticket prices

If only one person is making the change to the database, there would be no problem....

Guaranteeing transaction integrity with database locks

Figure 8.36 illustrates how a row-level lock ensures that the transaction is completed first before the next one is run:

Figure 8.36 – Workflow with a row-level lock

Step 3 for Person A will add $10 to the price, making it $110. Because a row-level lock has been applied, Person B will not be able to update the price for Ticket X until the update operation of Person A has finished. Then, once the change has been made for Person A, the update operation of Person B will proceed (after waiting in the queue), adding the $15 to the correct revised price of $110, leading to $125. Amazing, right?

Note

Note that Person A and Person B have no idea that another concurrent operation is happening! Row-level locks simply ensure that even if multiple transactions are happening concurrently, the operations updating the same record would queue automatically and the numbers would still add up!

Here are...

Best practices when using database locks

When working with database locks, it is important to follow best practices to ensure smooth and efficient operations. Here are some best practices to keep in mind:

• Use locks when they are needed: Applying locks can affect performance, so only do it when it’s absolutely required. Reduce the time that locks are in place. Locks should be applied as late as possible, and then released as soon as they are no longer required.
• Use appropriate lock granularity: Instead of locking entire tables or large sections, lock the specific rows or objects that need protection. This limits the scope of the lock and improves concurrency.
• Understand transaction isolation levels: The acquisition and release of locks are affected by various isolation levels. Learn about the many isolation levels that are available and select the best one for your application’s concurrency and consistency requirements.
• Avoid deadlocks: The use of explicit...

Summary

In this chapter, we discussed how to get started with PostgreSQL by installing it, creating a database and a table, and then inserting data into the table. These are some of the basic concepts when working with databases, and by knowing these key concepts, you can build on them to create more ways to work with the data. Next, we discussed how race conditions affect transaction integrity for financial systems, and how database locks prevent financial transaction integrity issues. After that, we discussed how to guarantee transaction integrity with database locks, with the use of row-level locks. Lastly, we discussed the best practices when using database locks to ensure smooth and efficient operations. By understanding how database locks work, we can improve the data integrity and performance of our applications.

In the next chapter, we will discuss how to use managed ledger databases, specifically Amazon Quantum Ledger Database (QLDB), which is a non-relational database...

Further reading

To dive deeper into the topics we discussed in this chapter, feel free to explore these additional resources:

• PostgreSQL documentation on explicit locking:

  https://www.postgresql.org/docs/current/explicit-locking.html

• Why use PostgreSQL?:

  https://www.postgresql.org/about/

• PostgreSQL Tutorial:

  https://www.postgresqltutorial.com/