Software Development Blog

Introduction In this article we are going to look at various implementations of bank transfer in a web applciation API taking into consideration data consistency and performance.  Technologies and software I am using in examples below are: PostgreSQL, Java 17, JDBC, Spark Java, JMeter, Postman, IntelliJ Approach 1 - plain SQLs We start by creating a following piece of code to perform a bank transfer: In terms of DB operations we can write it in following way: SELECT source account balance SELECT target account balance UPDATE source account balance UPDATE target account balance We can test how will it behave when there are multiple users performing transfers concurrently. In my case I am going to place this code in a simple Jetty server and use JMeter to simulate the traffic.  Here is the JMeter load testing thread config (please note that same user on each iteration is unchecked): with web server request config: and payload: As you can see we will perform 100 POST request

Content MVCC Xmin and Xmax VACUUM command.  Locks Transaction isolation levels Used tools: PostgreSQL setup with Docker:  https://hub.docker.com/_/postgres pgAdmin:  https://www.pgadmin.org/ MVCC PostgreSQL uses multiversion concurrency control (MVCC) architecture to implement transactions with proper isolation levels. MVCC makes concurrent access possible and safe by maintaining several versions of a row. Row is enriched with additional metadata which is xmin and xmax. Lets take a look at the example: Xmin and xmax I will insert a new row into the table: We can do a standard select: With followig result: Nothing unexpected. But lets see the result of SELECT that explicitly mentions xmin and xmax columns:   We can now see two new columns in the result: These columns were not added by me when creating the table. They are handled by MVCC mechanism in PostgreSQL. As the official documentation states: xmin - The identity (transaction ID) of the inserting transa