Skip site navigation (1) Skip section navigation (2)

Postgresql ACID bug?

From: Jan Snelders <jan(at)snelders(dot)net>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Postgresql ACID bug?
Date: 2011-08-30 13:18:56
Message-ID: efb6742f2aaebfc7e8de4f6e413c9268@mail.snelders.net (view raw or flat)
Thread:
Lists: pgsql-bugs

Introduction
I wrote small java program which performs some selects in
one transaction (and thread) and one delete in another transaction and
thread on the same table holding one record initially. 

ENVIRONMENT
OS:
Centos 5.5 kernel 2.6.18-194.32.1.el5
Postgresql: 9.0.4
jdbc driver:
9.0-801 JDBC 4 

RESULTS AND EXPECTATIONS:
 The program starts with a
select (SELECT transaction) and prints one record ('id' and 'address' from
RelationAddresses table)
 The program now deletes the record in another
transaction (DELETE TRANSACTION)
 The program now selects and prints the
records from the SELECT transaction. (result as expected one record
printed)
 The DELETE transaction commits.
 The program now selects and
prints the records from the SELECT transaction. (0 records printed, we
expected one record since we are still within the SELECT transaction which
started while this record was still available)

 [code]
import
java.sql.Connection;
 import java.sql.DriverManager;
 import
java.sql.ResultSet;
 import java.sql.SQLException;
 import
java.sql.Statement;

 public class TransactionTest {
 /**/
 private static
final String driver = "org.postgresql.Driver";
 private static final String
connection = "jdbc:postgresql://192.168.0.1/sampledb";
 private static
final String username = "postgres";
 private static final String password =
"secret";
 private static final String escapeChar = """;
 /**/

 /*

private static final String driver = "com.mysql.jdbc.Driver";
 private
static final String connection = "jdbc:mysql://192.168.0.1/sampledb";

private static final String username = "root";
 private static final String
password = "secret";
 private static final String escapeChar = "`";
 /**/


private TransactionTest(){

 Select select = new Select();

select.start();

 try {
 Thread.sleep(1000);
 } catch (InterruptedException
e) { /*IGNORE*/ }

 Delete delete = new Delete();
 delete.start();
 }


public static void main(String[] args) {
 new TransactionTest();
 }


Object block = new Object();
 class Select extends Thread{
 @Override

public void run(){
 try {
 Class.forName(driver).newInstance();
 Connection
conn = DriverManager.getConnection(connection, username, password);

conn.setAutoCommit(false);

 System.out.println("------------");

System.out.println("SELECT TRANSACTION: List all addresses:");
 String
query = "SELECT " + escapeChar + "relationAddressesId" + escapeChar + ", "
+ escapeChar + "address" + escapeChar + " FROM " + escapeChar +
"RelationAddresses" + escapeChar + "";
 Statement stmt =
conn.createStatement();
 ResultSet rs = stmt.executeQuery(query);

while(rs.next()){
 System.out.println("relationAddressesId: " +
rs.getInt(1) + " - address: " + rs.getString(2));
 }
 rs.close();
stmt.close();
 System.out.println("------------");

 synchronized(block){

try {
 block.wait();
 } catch (InterruptedException e) { /*IGNORE*/}
 }


System.out.println("------------");
 System.out.println("SELECT
TRANSACTION: List all addresses before DELETE TRANSACTION commit():");

query = "SELECT " + escapeChar + "relationAddressesId" + escapeChar + ", "
+ escapeChar + "address" + escapeChar + " FROM " + escapeChar +
"RelationAddresses" + escapeChar + "";
 stmt = conn.createStatement();
 rs
= stmt.executeQuery(query);
 while(rs.next()){

System.out.println("relationAddressesId: " + rs.getInt(1) + " - address: "
+ rs.getString(2));
 }
 rs.close(); stmt.close();

System.out.println("------------");

 synchronized(block){
 try {

block.wait();
 } catch (InterruptedException e) { /*IGNORE*/}
 }


System.out.println("------------");
 System.out.println("SELECT
TRANSACTION: List all addresses after DELETE TRANSACTION commit():");

query = "SELECT " + escapeChar + "relationAddressesId" + escapeChar + ", "
+ escapeChar + "address" + escapeChar + " FROM " + escapeChar +
"RelationAddresses" + escapeChar + "";
 stmt = conn.createStatement();
 rs
= stmt.executeQuery(query);
 while(rs.next()){

System.out.println("relationAddressesId: " + rs.getInt(1) + " - address: "
+ rs.getString(2));
 }
 rs.close(); stmt.close();

System.out.println("------------");

 conn.commit();
 } catch
(ClassNotFoundException e) {
 e.printStackTrace();
 } catch (SQLException
e) {
 e.printStackTrace();
 } catch (InstantiationException e) {

e.printStackTrace();
 } catch (IllegalAccessException e) {

e.printStackTrace();
 }
 }
 }

 class Delete extends Thread{
 @Override

public void run(){
 try {
 Class.forName(driver).newInstance();
 Connection
conn = DriverManager.getConnection(connection, username, password);

conn.setAutoCommit(false);

 System.out.println("");

System.out.println("DELETE TRANSACTION: Deleting addresses....");

System.out.println("");
 String query = "DELETE FROM " + escapeChar +
"RelationAddresses" + escapeChar + "";
 Statement stmt =
conn.createStatement();
 stmt.executeUpdate(query);
 stmt.close();


synchronized(block){
 block.notifyAll();
 }

 try {
 Thread.sleep(1000);
 }
catch (InterruptedException e) {/*IGNORE*/}
 conn.commit();


synchronized(block){
 block.notifyAll();
 }
 } catch
(ClassNotFoundException e) {
 e.printStackTrace();
 } catch (SQLException
e) {
 e.printStackTrace();
 } catch (InstantiationException e) {

e.printStackTrace();
 } catch (IllegalAccessException e) {

e.printStackTrace();
 }
 }
 }
 }
 [/code]

EXPECTED OUTPUT:
 ------------

SELECT TRANSACTION: List all addresses:
 relationAddressesId: 1 - address:
Some address
 ------------

 DELETE TRANSACTION: Deleting addresses....


------------
 SELECT TRANSACTION: List all addresses before DELETE
TRANSACTION commit():
 relationAddressesId: 1 - address: Some address

------------
 ------------
 SELECT TRANSACTION: List all addresses after
DELETE TRANSACTION commit():
 relationAddressesId: 1 - address: Some
address
 ------------

ACTUAL OUTPUT:
 ------------
 SELECT TRANSACTION:
List all addresses:
 relationAddressesId: 1 - address: Some address

------------

 DELETE TRANSACTION: Deleting addresses....

 ------------

SELECT TRANSACTION: List all addresses before DELETE TRANSACTION commit():

relationAddressesId: 1 - address: Some address
 ------------
 ------------

SELECT TRANSACTION: List all addresses after DELETE TRANSACTION commit():

------------

Responses

pgsql-bugs by date

Next:From: Craig RingerDate: 2011-08-30 14:16:33
Subject: Re: Postgresql ACID bug?
Previous:From: Bhavesh DangiDate: 2011-08-30 11:28:49
Subject: BUG #6187: Select query stuck

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group