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