Received: from p1.selectacast.net (p1.selectacast.net [206.41.1.251])
	by hub.org (8.10.1/8.10.1) with ESMTP id e6DIE5075769
	for <pgsql-general@hub.org>; Thu, 13 Jul 2000 14:14:05 -0400 (EDT)
Received: from selectacast.net (IDENT:jks@d1 [209.219.91.66])
	by p1.selectacast.net (8.9.3/8.9.3) with ESMTP id OAA30531;
	Thu, 13 Jul 2000 14:12:59 -0400
Message-ID: <396E06AB.7544C109@selectacast.net>
Date: Thu, 13 Jul 2000 14:12:59 -0400
From: Joseph Shraibman <jks@selectacast.net>
Organization: Xtenit
X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.15 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "pgsql-general@hub.org" <pgsql-general@hub.org>
Subject: Re: select for update not locking properly.
References: <396D2AEA.5D19984A@selectacast.net>
 <28676.963473660@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200007/578

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