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?
lock aquired ...
output of the second session show the new lock time straight away or is there a delay?