From: Joseph Shraibman <jks@selectacast.net>
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.
Date: Thu, 13 Jul 2000 14:12:59 -0400
Message-ID: <396E06AB.7544C109@selectacast.net> (raw)
References: <396D2AEA.5D19984A@selectacast.net>
<28676.963473660@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: jks@selectacast.net, tgl@sss.pgh.pa.us, pgsql-general@hub.org
Subject: Re: select for update not locking properly.
In-Reply-To: <396E06AB.7544C109@selectacast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox