Thread: select for update not locking properly.
I have a program that does this: BEGIN; SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE; <my program takes mystring and appends to it> UPDATE mytable SET mystring = '<appended string here>' WHERE x = 3 AND y = 4; END; But the locking isn't working properly. I do something that should cause 3 different threads to try and do that append, and the first one goes through properly, but the second two append to the result of the first on only, meaning that the third one didn't see the result of the second's append. I tried to make a simple example that reproduced this, but failed. I'm guessing that the value of that the select is generated before the row is locked, and thus each of my last two threads saw the table after the first append, *then* one of them blocked because the other had locked the table. It's the only thing I can figure.
And I forgot to mention my version is: PostgreSQL 7.0.1 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 Joseph Shraibman wrote: > > I have a program that does this: > BEGIN; > SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE; > <my program takes mystring and appends to it> > UPDATE mytable SET mystring = '<appended string here>' WHERE x = 3 AND y > = 4; > END; > > But the locking isn't working properly. I do something that should > cause 3 different threads to try and do that append, and the first one > goes through properly, but the second two append to the result of the > first on only, meaning that the third one didn't see the result of the > second's append. > > I tried to make a simple example that reproduced this, but failed. > > I'm guessing that the value of that the select is generated before the > row is locked, and thus each of my last two threads saw the table after > the first append, *then* one of them blocked because the other had > locked the table. It's the only thing I can figure.
Joseph Shraibman <jks@selectacast.net> writes:
> But the locking isn't working properly. I do something that should
> cause 3 different threads to try and do that append, and the first one
> goes through properly, but the second two append to the result of the
> first on only, meaning that the third one didn't see the result of the
> second's append.
Can't duplicate it here. I did:
<in psql window 1>
regression=# create table mytable(mystring text, x int, y int);
CREATE
regression=# insert into mytable values('z',1,4);
INSERT 399867 1
regression=# insert into mytable values('foo',3,4);
INSERT 399868 1
regression=# begin;
BEGIN
regression=# SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE;
mystring
----------
foo
(1 row)
<in psql window 2>
regression=# begin;
BEGIN
regression=# SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE;
[ psql hangs ]
<in psql window 3>
regression=# begin;
BEGIN
regression=# SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE;
[ psql hangs ]
<back to psql window 1>
regression=# UPDATE mytable SET mystring = 'foo bar' WHERE x = 3 AND y = 4;
UPDATE 1
regression=# end;
COMMIT
regression=#
<psql window 3 now responds>
mystring
----------
foo bar
(1 row)
regression=# UPDATE mytable SET mystring = 'foo bar baz' WHERE x = 3 AND y = 4;
UPDATE 1
regression=# end;
COMMIT
regression=#
<psql window 2 now responds>
mystring
-------------
foo bar baz
(1 row)
regression=#
Looks pretty cool to me...
regards, tom lane
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
Joseph Shraibman <jks@selectacast.net> writes:
> OK here is the test program.
I don't know Java hardly at all, but it looks like you've got ten
threads in Java all issuing commands through a *single* connection
to a single backend. Postgres isn't going to lock those threads
against each other for you ... it has no idea whatever that the
sequence of commands it's seeing aren't all from one thread.
You'd need to have ten separate connections to ten separate backends
to get the behavior you're expecting. Try putting the Connection
objects into the Adder objects and firing them up at Adder creation.
regards, tom lane
Damn, I though having seperate Statement objects was supposed to take care of that. Peter can you confirm this? Tom Lane wrote: > > Joseph Shraibman <jks@selectacast.net> writes: > > OK here is the test program. > > I don't know Java hardly at all, but it looks like you've got ten > threads in Java all issuing commands through a *single* connection > to a single backend. Postgres isn't going to lock those threads > against each other for you ... it has no idea whatever that the > sequence of commands it's seeing aren't all from one thread. > > You'd need to have ten separate connections to ten separate backends > to get the behavior you're expecting. Try putting the Connection > objects into the Adder objects and firing them up at Adder creation. > > regards, tom lane