Postgresql – How locks in Postgres behave when quitting the JVM abnormally

lockingpostgresqlsql-standard

I have a table 'car', as follows

                 Table "public.car"
  Column  |  Type  | Collation | Nullable | Default 
----------+--------+-----------+----------+---------
 car_no   | bigint |           | not null | 
 car_name | text   |           |          | 
Indexes:
    "car_pkey" PRIMARY KEY, btree (car_no)

Table content:

 car_no |  car_name  
--------+------------
      1 | first_car
      2 | second_car

I have a JAVA code, which locks a row in database and quits abnormally as follows,

public static void main(String[] args) throws SQLException {
        Connection connection = 
                DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "root", "root");

        // begin transaction
        connection.setAutoCommit(false);

        System.out.println("waiting for lock "+new Date(System.currentTimeMillis()));
        // getting lock
        connection.createStatement().executeQuery("select * from car where car_no=2 for update");
        System.out.println("lock acquired "+new Date(System.currentTimeMillis()));
        System.out.println("press enter to quit abnormally [System.exit(1)]:");
        Scanner scanner = new Scanner(System.in);
        scanner.nextLine();
        System.out.println("terminating execution "+new Date(System.currentTimeMillis()));

        // quiting JVM abnormally
        System.exit(1);

        System.out.println("this doesn't prints");
}

When i executed the code snippet concurrently (two times), the first execution takes the row lock (waiting for System.exit), the second execution waits for the row lock. When first one quits abnormally, the second execution got the required lock. I wonder what happened to locks acquired by first execution, are they returned to database since abnormal termination or something else. I need some insights on row locking during these kind of scenario. Does it depends on transaction isolation level?

Best Answer

The answer depends on a few things and my Java knowledge isn't up-to-date regarding session handling.

However, the lock isn't held by the database, the lock is held by a session.

This means if the session no longer exists, then the lock is removed from the database object (table, row, ...).

If you were to run the Java code on a different server/computer, then you might observe a delay before the second message is displayed. This will be because the JDBC connection could be using different parameters.

  • socketTimeout

    The timeout value used for socket read operations. If reading from the server takes longer than this value, the connection is closed. This can be used as both a brute force global query timeout and a method of detecting network problems. The timeout is specified in seconds and a value of zero means that it is disabled.

Answering Your Question

I need some insights on row locking during these kind of scenario. Does it depends on transaction isolation level?

Session terminated = Lock removed

If you remove the for update part, then the second statement would be able to read the data at the same time. As soon as you are expecting to update something, then you will have a ROW EXCLUSIVE lock.

The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any command that modifies data in a table.

and

Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.

Reference: 13.3. Explicit Locking (PostgreSQL 9.4 Documentation)

Further Reading/Reference Material

PostgreSQL Concurrency Model

PostgreSQL provides a rich set of tools for developers to manage concurrent access to data. Internally, data consistency is maintained by using a multiversion model (Multiversion Concurrency Control, MVCC). This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This prevents statements from viewing inconsistent data produced by concurrent transactions performing updates on the same data rows, providing transaction isolation for each database session. MVCC, by eschewing the locking methodologies of traditional database systems, minimizes lock contention in order to allow for reasonable performance in multiuser environments.

Reference: Chapter 13. Concurrency Control - 13.1. Introduction (PostgreSQL 9.4 Documentation)

PostgreSQL JDBC Driver

PostgreSQL™ provides a type 4 JDBC driver. Type 4 indicates that the driver is written in Pure Java, and communicates in the database system's own network protocol. Because of this, the driver is platform independent; once compiled, the driver can be used on any system.

Reference: The PostgreSQL JDBC Interface- Chapter 1. Introduction (PostgreSQL JDBC Driver Documentation)

Related Question