Re: select for update not locking properly.

OK here is the test program.

To run
(1) in psql:
once:
create table locktest (x int, y int , list text, primary key (x,
y));
insert into locktest values(3, 4, NULL);
between runs:
update locktest set list = null where x = 3 and y = 4 ;

(2) customize the code below to include a valid username, password, and
url. For those of you not familiar with jdbc the url is
"jdbc:postgres:<dbname>"

(3) run from the command line like so:
java PostgresTest 4 10

Last time I ran it I got this:
About to sleep... done. List now is:
null<A:0><A:1><A:2><A:3><A:4><A:5><B:0><B:1><B:2><C:1><B:5><B:6><B:7><B:8><B:9><D:5><D:6><C:9><D:8><D:9>

There should have been one of <letter:0-9> but obviously some are
missing.

Here is the end of my postres log. See how the changes are overwriting
each other.

query: BEGIN ;
ProcessUtility: BEGIN ;
query: SELECT list FROM locktest WHERE x = 3 AND y = 4 FOR UPDATE;
query: END ;
ProcessUtility: END ;
query: BEGIN ;
ProcessUtility: BEGIN ;
query: SELECT list FROM locktest WHERE x = 3 AND y = 4 FOR UPDATE;
query: UPDATE locktest SET list =
'null<A:0><A:1><A:2><A:3><A:4><A:5><B:0><B:1><B:2><C:1><B:5><B:6><B:7><B:8><B:9><D:5><D:6><D:7>'
WHERE x = 3 AND y = 4 ;
query: UPDATE locktest SET list =
'null<A:0><A:1><A:2><A:3><A:4><A:5><B:0><B:1><B:2><C:1><B:5><B:6><B:7><B:8><B:9><D:5><D:6><C:9>'
=================================== !!! See! C:9 overwrites D:7 !!!
==================================
WHERE x = 3 AND y = 4 ;
query: END ;
ProcessUtility: END ;
query: END ;
ProcessUtility: END ;
NOTICE: COMMIT: no transaction in progress
=================================== !!! Why this message all of a
sudden? !!! ==================================
query: BEGIN ;
ProcessUtility: BEGIN ;
query: SELECT list FROM locktest WHERE x = 3 AND y = 4 FOR UPDATE;
query: UPDATE locktest SET list =
'null<A:0><A:1><A:2><A:3><A:4><A:5><B:0><B:1><B:2><C:1><B:5><B:6><B:7><B:8><B:9><D:5><D:6><C:9><D:8>'
WHERE x = 3 AND y = 4 ;
query: END ;
ProcessUtility: END ;
query: BEGIN ;
ProcessUtility: BEGIN ;
query: SELECT list FROM locktest WHERE x = 3 AND y = 4 FOR UPDATE;
query: UPDATE locktest SET list =
'null<A:0><A:1><A:2><A:3><A:4><A:5><B:0><B:1><B:2><C:1><B:5><B:6><B:7><B:8><B:9><D:5><D:6><C:9><D:8><D:9>'
WHERE x = 3 AND y = 4 ;
query: END ;
ProcessUtility: END ;
query: SELECT list FROM locktest WHERE x = 3 AND y = 4 ;

==============================================================================================================
/**
* PostgresTest.java
*
*
* Created: Thu Jul 13 13:20:25 2000
*
* @author Joseph Shraibman
* @version 1.0
*/
import java.sql.*;

public class PostgresTest {
protected final static String alphabet =
"ABCDEFGHIJKLMNOPQRXTUVWXYZ"; //names for threads.

//these values will have to be customized for each machine
private static final String url = "jdbc:postgresql:playpen";
private static final String usr = "";
private static final String pwd = "";

int loopvar = 10; //how many times each thread will do an append
int numt = 10; //number of threads

protected Connection db; // The connection to the database

public PostgresTest() throws Exception{
Class.forName("org.postgresql.Driver");

db = DriverManager.getConnection(url, usr, pwd);
}

public void getGoing(){
for (int i = 0; i < numt; i++){
Adder a = new Adder();
a.name = String.valueOf(alphabet.charAt(i));
(new Thread(a)).start();
}
}

public String getList() throws Exception{
String ans = null;
Statement st = db.createStatement();
ResultSet rs = st.executeQuery("SELECT list FROM locktest WHERE x = 3
AND y = 4 ;");
if (! rs.next()){
System.err.println("rs.next() returned false!!");
} else
ans = rs.getString(1);
st.close();
return ans;
}

private class Adder implements Runnable {
String name;
public void run(){
for (int i = 0; i < loopvar; i++)
add(i);
}
public void add(int i){
try{
Statement st = db.createStatement();
st.executeUpdate("BEGIN ;") ;
ResultSet rs = st.executeQuery("SELECT list FROM locktest WHERE x = 3
AND y = 4 FOR UPDATE;");
if (! rs.next()){
System.err.println("rs.next() returned false!!");
} else{
String list = rs.getString(1);

//now do some meaningless work to waste time
int x = 0;
for (int j = 0 ; j < 1000000; j++)
x += j;

list += "<"+name+":"+i+">";
int num = st.executeUpdate("UPDATE locktest SET list = '"+list+"'
WHERE x = 3 AND y = 4 ;");
if (num != 1)
System.err.println("WARNING!! num is "+num);

}
st.executeUpdate("END ;") ;
st.close();

}catch(SQLException e){
e.printStackTrace();
}
}
}
//usage: java PostgresTest <treads> <loops>
public static void main(String[] args) throws Exception{
PostgresTest pt = new PostgresTest();

if (args.length >= 1)
pt.numt = Integer.parseInt(args[0]);
if (args.length >= 2)
pt.loopvar = Integer.parseInt(args[1]);

pt.getGoing();

System.out.print("About to sleep...");
System.out.flush();

Thread.sleep(pt.loopvar * pt.numt * 250); //should be more than enough
time;

System.out.println(" done. List now is:\n"+pt.getList());

pt.db.close();

}

} // PostgresTest