Thread: Postgresql ACID bug?
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():
------------
Jan Snelders <jan@snelders.net> wrote: > 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. > 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) You are probably running at the READ COMMITTED transaction isolation level. The behavior you describe is allowed at that isolation level, both by the standard and by PostgreSQL. You seem to be expecting that the default transaction isolation level will be SERIALIZABLE (which is not altogether unreasonable, since that is what the standard requires); but PostgreSQL has a default configuration of defaulting to the READ COMMITTED level. You can edit your postgresql.conf file to specify default_transaction_isolation = 'serializable' to get standard conforming behavior. Note that there are still some corner cases where you don't get full ACID behavior at the SERIALIZABLE level in PostgreSQL version 9.0; this has been enhanced to fully compliant behavior in the upcoming 9.1 release. For 9.0 behavior, see: http://www.postgresql.org/docs/9.0/static/transaction-iso.html For behavior in the soon-to-be-release 9.1 version, see: http://www.postgresql.org/docs/9.1/static/transaction-iso.html -Kevin
On 30/08/2011 9:18 PM, Jan Snelders wrote: > 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) This isn't a bug. You're using READ COMMITTED mode, and you appear to expect SERIALIZABLE mode. See: http://www.postgresql.org/docs/current/interactive/transaction-iso.html -- Craig Ringer