Hello all,

I just discovered PLpgSQL yesterday (yay!) as we began development of our 
more robust database backend, but I'm seeing some odd behavior in the use 
of LOCK TABLE. The problem I'm seeing is that two database transactions, 
initiated via JDBC, are able to obtain simultaneous exclusive table locks 
on the same table. If I lock the table in PSQL, the JDBC calls do indeed 
block until I end my transaction in PSQL. However, two JDBC calls don't 
appear to block each other. :(

There may indeed be a better solution than locking a table for what I'm 
doing, so please chime in if I'm missing something there. My next attempt 
will be to wrap the whole select-update in a loop, but I'm afraid of 
creating a deadlock situation.

I used PLpgSQL to implement a next_id function that allows IDs to be 
allocated in continuous blocks (thus I believe I cannot use sequences). The 
client application generates its own IDs but tracks them in the database. 
To minimize DB calls the client allocates IDs in blocks and doles them out 
one-at-a-time. Thus, 20 calls to IDFactory.nextID() in Java will result in 
only 2 calls to the database function next_id_block(10).

As well each object type (there are 6) that needs IDs gets its own 
IDFactory, *and* there are multiple Java clients accessing the same 
database. Therefore, it's quite possible for two IDFactories to call 
next_id_block(count) at the same time. Thus I'm locking the table in 
exclusive mode to force synchronous access. I've also tried access 
exclusive mode to no avail.

Here's the table definition:

     create table idfactory
     (
       name              varchar(20)   not null    primary key,
       next_id           integer       not null    default 1,
       change_num        smallint      not null    default 1
     ) ;

This is the psuedo-code algorithm I've implemented in PLpgSQL:

     next_id_block ( count )
     (1)   lock idfactory table

     (2)   read idfactory row
     (3)   update idfactory row
               increment next_id by count
               increment change_num by 1
           where change_num is equal to that read in (2)

     (4)   FAIL if (3) updated 0 rows

     (5)   return next_id read in (2)

My intent is that by locking the idfactory table, I can assure that no one 
else can update the row between it being read in step 2 and updated in step 
3. I've tried calling this function from JDBC with auto-commit on as well 
as with it off and the connection set to both transaction levels. The 
reality, however, is that some threads are trying to update the row 
concurrently and failing (0 rows updated since the change_num value no 
longer matches.

I'll try to illustrate what seems to be happening in the case of two threads.

     Time    Thread 1      Thread 2
      1      lock
      2      read 1, 1
      3                    lock
      4                    read 1, 1
      5      write 11, 2
      6                    write 11, 2
      7      return 1
      8                    FAIL

It's my understanding that thread 2 should block at T3 since thread 1 
locked the table at T1. Thread 2 shouldn't continue until thread 1's 
transaction is ended (either by commit or abort). True? The truly odd part 
is that if I start up PSQL, begin a transaction, and then lock the table, 
all the threads calling the function block until I end the transaction, 
just as I'd expect. However, the threads won't block each other!

Here's the stored function itself:

     create function next_id_block (
       varchar , integer
     )
     returns bigint
     as '
     DECLARE
       -- Parameters
       name_key          alias for $1 ;
       block_size        alias for $2 ;

       -- Constants
       FIRST_ID          constant bigint := 1 ;

       -- Locals
       id_rec            record ;
       new_last_id       bigint ;
       num_rows          integer ;
     BEGIN
       -- To avoid a retry-loop, lock the whole table for the transaction
       lock table idfactory in access exclusive mode ;

       -- Read the current value of next_id
       select into id_rec * from idfactory where name = name_key ;

       -- Increment it by block_size
       new_last_id := id_rec.next_id + block_size ;
       update idfactory
           set next_id = new_last_id,
               change_num = change_num + 1
           where name = name_key and change_num = id_rec.change_num ;

       -- Error if filter not found
       get diagnostics num_rows = ROW_COUNT ;
       if num_rows != 1 then
         raise exception ''Failed to update idfactory.next_id to % for % at 
%'',
             new_last_id, name_key, id_rec.change_num;
         return -1 ;
       end if ;

       return id_rec.next_id ;
     END ;
     ' language 'plpgsql' with (isstrict) ;

Finally, here's the JDBC code I'm using to call it:

     protected void allocate ( int count )
     {
       PreparedStatement     stmt = null;
       ResultSet             result = null;
       long                  newNextID = INVALID_ID;

       try
       {
         stmt = conn.prepareStatement("select next_id_block(?, ?)");

         stmt.setString(1, name);
         stmt.setInt(2, count);

     //    conn.setAutoCommit(false);
         result = stmt.executeQuery();

         if ( ! result.next() )
           throw new SQLException("Function next_id_block failed");

         // Pull out the new value and close the result set.
         newNextID = Nulls.getLong(result, 1);

         try { result.close(); result = null; }
         catch ( SQLException ignore ) { }

     //    conn.commit();

         // Null values are not allowed.
         if ( Nulls.is(newNextID) )
           throw new SQLException("Function next_id_block returned null");

         nextID = newNextID;
         lastID = nextID + count;
       }
       catch ( SQLException e )
       {
         e.printStackTrace();
       }
       finally
       {
         if ( result != null )
         {
           try { result.close(); }
           catch ( SQLException ignore ) { }
         }
       }
     }

Anyway, this was rather long, but I wanted to provide all the information 
necessary up front. Thank you for any thoughts or ideas you might have.

Peace,
Dave

--
David Harkness
MEconomy, Inc.


Tue, 02 Oct 2001 13:31:27 -0700
Dave Harkness <daveh@MEconomy.com>
LOCK TABLE oddness in PLpgSQL function called via JDBC
Dave,

First off, are you running with autocommit turned off in JDBC?  By 
default autocommit is on, and thus your lock is removed as soon as it is 
aquired.

Secondly, you don't need a table lock, you just need to lock the row 
between the select and the update.  You should use 'select for update' 
to do this.  That way when you issue the select to get the current 
value, it will lock the row, preventing other select for update requests 
from completing until the lock is released.  That way the select and the 
update can be assured that no one else is changing the data.

thanks,
--Barry


Dave Harkness wrote:

> Hello all,
> 
> I just discovered PLpgSQL yesterday (yay!) as we began development of 
> our more robust database backend, but I'm seeing some odd behavior in 
> the use of LOCK TABLE. The problem I'm seeing is that two database 
> transactions, initiated via JDBC, are able to obtain simultaneous 
> exclusive table locks on the same table. If I lock the table in PSQL, 
> the JDBC calls do indeed block until I end my transaction in PSQL. 
> However, two JDBC calls don't appear to block each other. :(
> 
> There may indeed be a better solution than locking a table for what I'm 
> doing, so please chime in if I'm missing something there. My next 
> attempt will be to wrap the whole select-update in a loop, but I'm 
> afraid of creating a deadlock situation.
> 
> I used PLpgSQL to implement a next_id function that allows IDs to be 
> allocated in continuous blocks (thus I believe I cannot use sequences). 
> The client application generates its own IDs but tracks them in the 
> database. To minimize DB calls the client allocates IDs in blocks and 
> doles them out one-at-a-time. Thus, 20 calls to IDFactory.nextID() in 
> Java will result in only 2 calls to the database function 
> next_id_block(10).
> 
> As well each object type (there are 6) that needs IDs gets its own 
> IDFactory, *and* there are multiple Java clients accessing the same 
> database. Therefore, it's quite possible for two IDFactories to call 
> next_id_block(count) at the same time. Thus I'm locking the table in 
> exclusive mode to force synchronous access. I've also tried access 
> exclusive mode to no avail.
> 
> Here's the table definition:
> 
>     create table idfactory
>     (
>       name              varchar(20)   not null    primary key,
>       next_id           integer       not null    default 1,
>       change_num        smallint      not null    default 1
>     ) ;
> 
> This is the psuedo-code algorithm I've implemented in PLpgSQL:
> 
>     next_id_block ( count )
>     (1)   lock idfactory table
> 
>     (2)   read idfactory row
>     (3)   update idfactory row
>               increment next_id by count
>               increment change_num by 1
>           where change_num is equal to that read in (2)
> 
>     (4)   FAIL if (3) updated 0 rows
> 
>     (5)   return next_id read in (2)
> 
> My intent is that by locking the idfactory table, I can assure that no 
> one else can update the row between it being read in step 2 and updated 
> in step 3. I've tried calling this function from JDBC with auto-commit 
> on as well as with it off and the connection set to both transaction 
> levels. The reality, however, is that some threads are trying to update 
> the row concurrently and failing (0 rows updated since the change_num 
> value no longer matches.
> 
> I'll try to illustrate what seems to be happening in the case of two 
> threads.
> 
>     Time    Thread 1      Thread 2
>      1      lock
>      2      read 1, 1
>      3                    lock
>      4                    read 1, 1
>      5      write 11, 2
>      6                    write 11, 2
>      7      return 1
>      8                    FAIL
> 
> It's my understanding that thread 2 should block at T3 since thread 1 
> locked the table at T1. Thread 2 shouldn't continue until thread 1's 
> transaction is ended (either by commit or abort). True? The truly odd 
> part is that if I start up PSQL, begin a transaction, and then lock the 
> table, all the threads calling the function block until I end the 
> transaction, just as I'd expect. However, the threads won't block each 
> other!
> 
> Here's the stored function itself:
> 
>     create function next_id_block (
>       varchar , integer
>     )
>     returns bigint
>     as '
>     DECLARE
>       -- Parameters
>       name_key          alias for $1 ;
>       block_size        alias for $2 ;
> 
>       -- Constants
>       FIRST_ID          constant bigint := 1 ;
> 
>       -- Locals
>       id_rec            record ;
>       new_last_id       bigint ;
>       num_rows          integer ;
>     BEGIN
>       -- To avoid a retry-loop, lock the whole table for the transaction
>       lock table idfactory in access exclusive mode ;
> 
>       -- Read the current value of next_id
>       select into id_rec * from idfactory where name = name_key ;
> 
>       -- Increment it by block_size
>       new_last_id := id_rec.next_id + block_size ;
>       update idfactory
>           set next_id = new_last_id,
>               change_num = change_num + 1
>           where name = name_key and change_num = id_rec.change_num ;
> 
>       -- Error if filter not found
>       get diagnostics num_rows = ROW_COUNT ;
>       if num_rows != 1 then
>         raise exception ''Failed to update idfactory.next_id to % for % 
> at %'',
>             new_last_id, name_key, id_rec.change_num;
>         return -1 ;
>       end if ;
> 
>       return id_rec.next_id ;
>     END ;
>     ' language 'plpgsql' with (isstrict) ;
> 
> Finally, here's the JDBC code I'm using to call it:
> 
>     protected void allocate ( int count )
>     {
>       PreparedStatement     stmt = null;
>       ResultSet             result = null;
>       long                  newNextID = INVALID_ID;
> 
>       try
>       {
>         stmt = conn.prepareStatement("select next_id_block(?, ?)");
> 
>         stmt.setString(1, name);
>         stmt.setInt(2, count);
> 
>     //    conn.setAutoCommit(false);
>         result = stmt.executeQuery();
> 
>         if ( ! result.next() )
>           throw new SQLException("Function next_id_block failed");
> 
>         // Pull out the new value and close the result set.
>         newNextID = Nulls.getLong(result, 1);
> 
>         try { result.close(); result = null; }
>         catch ( SQLException ignore ) { }
> 
>     //    conn.commit();
> 
>         // Null values are not allowed.
>         if ( Nulls.is(newNextID) )
>           throw new SQLException("Function next_id_block returned null");
> 
>         nextID = newNextID;
>         lastID = nextID + count;
>       }
>       catch ( SQLException e )
>       {
>         e.printStackTrace();
>       }
>       finally
>       {
>         if ( result != null )
>         {
>           try { result.close(); }
>           catch ( SQLException ignore ) { }
>         }
>       }
>     }
> 
> Anyway, this was rather long, but I wanted to provide all the 
> information necessary up front. Thank you for any thoughts or ideas you 
> might have.
> 
> Peace,
> Dave
> 
> -- 
> David Harkness
> MEconomy, Inc.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 



Tue, 02 Oct 2001 13:45:53 -0700
Barry Lind <barry@xythos.com>
Re: LOCK TABLE oddness in PLpgSQL function called via JDBC
At 01:45 PM 10/2/2001, Barry Lind wrote:
>Dave,
>
>First off, are you running with autocommit turned off in JDBC?  By default 
>autocommit is on, and thus your lock is removed as soon as it is aquired.

I've tried it with auto-commit ON and OFF. With it off, I've tried it with 
READ_COMMITTED and SERIALIZABLE. All produce the same result.

However, my understanding is that each JDBC statement is executed within a 
single transaction when auto-commit is ON. I'm executing only one statement:

     select next_id_block(?, ?)

While the function does indeed execute multiple statements itself, aren't 
they all done inside a single transaction? If not, I must rethink our 
strategy as I had assumed that the PLpgSQL functions I wrote would be 
transactional.

>Secondly, you don't need a table lock, you just need to lock the row 
>between the select and the update.  You should use 'select for update' to 
>do this.  That way when you issue the select to get the current value, it 
>will lock the row, preventing other select for update requests from 
>completing until the lock is released.  That way the select and the update 
>can be assured that no one else is changing the data.

THANK YOU! That's what I thought, but the documentation was a bit light on 
the subject of SELECT ... FOR UPDATE. So to mirror it back to you, if I do

     next_id_block ( count )
     (1)   read idfactory row FOR UPDATE

     (2)   update idfactory row
               increment next_id by count
               increment change_num by 1
           where change_num is equal to that read in (1)

     (3)   return next_id read in (1)

is it safe to assume that the update in (2) will ALWAYS succeed since it 
would be impossible for any other transaction to read or update the row 
once it was selected for update?

Thanks for your help.

Peace,
Dave


Tue, 02 Oct 2001 14:09:27 -0700
Dave Harkness <daveh@MEconomy.com>
Re: LOCK TABLE oddness in PLpgSQL function called via JDBC
Dave Harkness <daveh@MEconomy.com> writes:
> The problem I'm seeing is that two database transactions, 
> initiated via JDBC, are able to obtain simultaneous exclusive table locks 
> on the same table.

Sounds to me like JDBC is feeding all your commands through a single
database connection, which means that what you think are independent
transactions are really not.  Better take a closer look at what you're
doing.

			regards, tom lane

Tue, 02 Oct 2001 17:22:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: LOCK TABLE oddness in PLpgSQL function called via JDBC 
At 02:22 PM 10/2/2001, Tom Lane wrote:
>Dave Harkness <daveh@MEconomy.com> writes:
> > The problem I'm seeing is that two database transactions,
> > initiated via JDBC, are able to obtain simultaneous exclusive table locks
> > on the same table.
>
>Sounds to me like JDBC is feeding all your commands through a single
>database connection, which means that what you think are independent
>transactions are really not.  Better take a closer look at what you're
>doing.

My test code creates multiple test threads and then starts them each. Each 
test thread (IDFactoryThread) creates its own Connection and IDFactory 
(which gets the connection). The test thread itself simply calls 
IDFactory.nextID() in a loop. I'm not using any connection pooling 
whatsoever. I'm using the built-in PostgreSQL JDBC driver alone.

Here's the code:

     public static void test ( int numThreads , String nameKey )
     {
       Thread[]      threads = new Thread[numThreads];

       for ( int i = 0 ; i < numThreads ; i++ )
       {
         threads[i] = new IDFactoryThread(i, nameKey);
       }

       for ( int i = 0 ; i < numThreads ; i++ )
       {
         threads[i].start();
       }
     }

     class IDFactoryThread extends Thread
     {
       Connection      conn = null;
       IDFactorySQL    factory = null;

       public IDFactoryThread ( int index , String nameKey )
       {
         super(Integer.toString(index));
         init(nameKey);
       }

       public void init ( String nameKey )
       {
         try
         {
           conn = DriverManager.getConnection(IDFactorySQLTest.DB_URL);
         }
         catch ( SQLException e )
         {
           System.out.println("Could not connect to the database");
           e.printStackTrace();
           System.exit(1);
         }

         factory = new IDFactorySQL(conn, nameKey, 
IDFactorySQLTest.BLOCK_SIZE);
       }

       public void run ( )
       {
         try
         {
           for ( int i = 0 ; i < IDFactorySQLTest.LOOP_COUNT ; i++ )
           {
             System.out.println(getName() + " - " + factory.next());
           }
         }
         catch ( IllegalStateException e )
         {
           e.printStackTrace();
           System.exit(1);
         }

         factory.close();
       }
     }

Thanks again!

Peace,
Dave


Tue, 02 Oct 2001 14:38:30 -0700
Dave Harkness <daveh@MEconomy.com>
Re: LOCK TABLE oddness in PLpgSQL function called via
Barry, Tom, et al,

Thanks for your help. I really appreciate it.

Okay, I changed the PLpgSQL function to use select for update rather than 
locking the table explicitly. Now I'm getting different errors. Running in 
auto-commit and read-committed modes, I am seeing the same error as before: 
thread A is updating the (locked) row between thread B selecting and then 
updating it. This causes thread B's update to affect 0 rows which I'm 
trying to avoid.

Running in serializable mode, I'm getting a Postgres exception:

     ERROR:  Can't serialize access due to concurrent update

It seems to me that the table locks grabbed in the PLpgSQL function aren't 
actually locking the tables. They check to make sure they can *get* the 
lock, but don't actually hold the lock. Same with the select for update. It 
makes sure it can get the lock, but still lets others get the same lock.

Anyway, here's how I'm doing my transaction level setting in Java. 
IDFactorySQL gets a name key (String) and Connection object in its 
constructor, which it passes to an internal init() method where it sets the 
transaction handling:

     protected void init ( Connection conn , String name )
     {
       this.conn = conn;
       this.name = name;

       try
       {
         // 
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
         conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
         conn.setAutoCommit(false);
       }
       catch ( SQLException e )
       {
         invalidate();
       }
     }

I've tried both transaction levels separately as well as not setting it at 
all [but still calling setAutoCommit(false)] which I understand should 
leave me with read-committed level. Then, before calling the PLpgSQL 
function next_id_block(), I've tried again setting auto-commit to false as 
well as not doing so:

       stmt = conn.prepareStatement("select next_id_block(?, ?)");

       stmt.setString(1, name);
       stmt.setInt(2, count);

       conn.setAutoCommit(false);
       result = stmt.executeQuery();
       ...
       conn.commit();

I roll back in the case of any SQLException, but at that point the test 
stops as it's broken. Any other ideas?

Peace,
Dave


Tue, 02 Oct 2001 15:03:56 -0700
Dave Harkness <daveh@MEconomy.com>
Re: LOCK TABLE oddness in PLpgSQL function called via JDBC
Dave Harkness <daveh@MEconomy.com> writes:
> Running in serializable mode, I'm getting a Postgres exception:
>      ERROR:  Can't serialize access due to concurrent update

Well, in that case my theory about it all being one transaction is
wrong; you couldn't get that error without a cross-transaction conflict.

> It seems to me that the table locks grabbed in the PLpgSQL function aren't 
> actually locking the tables. They check to make sure they can *get* the 
> lock, but don't actually hold the lock. Same with the select for update. It 
> makes sure it can get the lock, but still lets others get the same lock.

Once a lock has been grabbed, the *only* way it can be let go is to
end the transaction.  So my new theory is that the JDBC driver is
issuing an auto-commit at points where you're not expecting it.

I'm not familiar enough with the behavior of "setAutoCommit" and friends
to be sure what's happening; but if you turn on query logging in the
server you'll probably see the evidence soon enough.

			regards, tom lane

Tue, 02 Oct 2001 18:29:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: LOCK TABLE oddness in PLpgSQL function called via JDBC 
At 03:29 PM 10/2/2001, Tom Lane wrote:
>Once a lock has been grabbed, the *only* way it can be let go is to end 
>the transaction.

That's my understanding as well.

>So my new theory is that the JDBC driver is issuing an auto-commit at 
>points where you're not expecting it.

But I'm only issuing *one* JDBC statement:

     select next_id_block(?, ?)

Once it returns, I grab the single value from the ResultSet, close the 
ResultSet, and commit the transaction.

All of the SQL magic is being done by the PLpgSQL stored function on the 
backend. It's almost like the PLpgSQL function itself is running in 
auto-commit mode, but then I don't see how I could be getting a 
serialization error. And the docs say that the function will run in the 
caller's transaction, so I'm just confused.

My suspicion was that JDBC was somehow interacting oddly with PLpgSQL, but 
more and more it's looking like PLpgSQL is the culprit all on its own. I'll 
try posing the question to the general mailing list since there are none 
specific to stored procedure languages, or is there a more appropriate list?

>but if you turn on query logging in the server you'll probably see the 
>evidence soon enough.

Y'know, that's a very good idea. I haven't done that before -- is it fairly 
prominent in the online documentation? I'm off to find it now... Thanks.

Peace,
Dave


Tue, 02 Oct 2001 15:41:13 -0700
Dave Harkness <daveh@MEconomy.com>
Re: LOCK TABLE oddness in PLpgSQL function called via
Dave,

I don't know why you are seeing these problems with the lock table.  But 
the select for update should work for you.  (In my product I have done 
exactly the same thing you are trying to do using select for update with 
success).

I would add one minor comment on your description of the behavior of 
using select for update:

The select for update will block other 'select for updates' or 
'updates'.  It does not block other simple selects.  But that is fine 
for the purposes here.

thanks,
--Barry



Dave Harkness wrote:

> At 01:45 PM 10/2/2001, Barry Lind wrote:
> 
>> Dave,
>>
>> First off, are you running with autocommit turned off in JDBC?  By 
>> default autocommit is on, and thus your lock is removed as soon as it 
>> is aquired.
> 
> 
> I've tried it with auto-commit ON and OFF. With it off, I've tried it 
> with READ_COMMITTED and SERIALIZABLE. All produce the same result.
> 
> However, my understanding is that each JDBC statement is executed within 
> a single transaction when auto-commit is ON. I'm executing only one 
> statement:
> 
>     select next_id_block(?, ?)
> 
> While the function does indeed execute multiple statements itself, 
> aren't they all done inside a single transaction? If not, I must rethink 
> our strategy as I had assumed that the PLpgSQL functions I wrote would 
> be transactional.
> 
>> Secondly, you don't need a table lock, you just need to lock the row 
>> between the select and the update.  You should use 'select for update' 
>> to do this.  That way when you issue the select to get the current 
>> value, it will lock the row, preventing other select for update 
>> requests from completing until the lock is released.  That way the 
>> select and the update can be assured that no one else is changing the 
>> data.
> 
> 
> THANK YOU! That's what I thought, but the documentation was a bit light 
> on the subject of SELECT ... FOR UPDATE. So to mirror it back to you, if 
> I do
> 
>     next_id_block ( count )
>     (1)   read idfactory row FOR UPDATE
> 
>     (2)   update idfactory row
>               increment next_id by count
>               increment change_num by 1
>           where change_num is equal to that read in (1)
> 
>     (3)   return next_id read in (1)
> 
> is it safe to assume that the update in (2) will ALWAYS succeed since it 
> would be impossible for any other transaction to read or update the row 
> once it was selected for update?
> 
> Thanks for your help.
> 
> Peace,
> Dave
> 



Tue, 02 Oct 2001 18:03:26 -0700
Barry Lind <barry@xythos.com>
Re: LOCK TABLE oddness in PLpgSQL function called via JDBC
Dave,

I can't explain what is happening here.  I think the best next step is 
to turn on query logging on the server and look at the actual SQL 
statements being executed.  It really looks like some extra commits or 
rollbacks are occuring that is causing the locks to be released.

thanks,
--Barry

Dave Harkness wrote:

> Barry, Tom, et al,
> 
> Thanks for your help. I really appreciate it.
> 
> Okay, I changed the PLpgSQL function to use select for update rather 
> than locking the table explicitly. Now I'm getting different errors. 
> Running in auto-commit and read-committed modes, I am seeing the same 
> error as before: thread A is updating the (locked) row between thread B 
> selecting and then updating it. This causes thread B's update to affect 
> 0 rows which I'm trying to avoid.
> 
> Running in serializable mode, I'm getting a Postgres exception:
> 
>     ERROR:  Can't serialize access due to concurrent update
> 
> It seems to me that the table locks grabbed in the PLpgSQL function 
> aren't actually locking the tables. They check to make sure they can 
> *get* the lock, but don't actually hold the lock. Same with the select 
> for update. It makes sure it can get the lock, but still lets others get 
> the same lock.
> 
> Anyway, here's how I'm doing my transaction level setting in Java. 
> IDFactorySQL gets a name key (String) and Connection object in its 
> constructor, which it passes to an internal init() method where it sets 
> the transaction handling:
> 
>     protected void init ( Connection conn , String name )
>     {
>       this.conn = conn;
>       this.name = name;
> 
>       try
>       {
>         // 
> conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
>         conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
>         conn.setAutoCommit(false);
>       }
>       catch ( SQLException e )
>       {
>         invalidate();
>       }
>     }
> 
> I've tried both transaction levels separately as well as not setting it 
> at all [but still calling setAutoCommit(false)] which I understand 
> should leave me with read-committed level. Then, before calling the 
> PLpgSQL function next_id_block(), I've tried again setting auto-commit 
> to false as well as not doing so:
> 
>       stmt = conn.prepareStatement("select next_id_block(?, ?)");
> 
>       stmt.setString(1, name);
>       stmt.setInt(2, count);
> 
>       conn.setAutoCommit(false);
>       result = stmt.executeQuery();
>       ...
>       conn.commit();
> 
> I roll back in the case of any SQLException, but at that point the test 
> stops as it's broken. Any other ideas?
> 
> Peace,
> Dave
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 



Tue, 02 Oct 2001 18:13:14 -0700
Barry Lind <barry@xythos.com>
Re: LOCK TABLE oddness in PLpgSQL function called via JDBC
Dave Harkness wrote:
> 
> At 01:45 PM 10/2/2001, Barry Lind wrote:
> >Dave,
> >
> >Secondly, you don't need a table lock, you just need to lock the row
> >between the select and the update.  You should use 'select for update' to
> >do this.  That way when you issue the select to get the current value, it
> >will lock the row, preventing other select for update requests from
> >completing until the lock is released.  That way the select and the update
> >can be assured that no one else is changing the data.
> 
> THANK YOU! That's what I thought, but the documentation was a bit light on
> the subject of SELECT ... FOR UPDATE. So to mirror it back to you, if I do
> 
>      next_id_block ( count )
>      (1)   read idfactory row FOR UPDATE
> 
>      (2)   update idfactory row
>                increment next_id by count
>                increment change_num by 1
>            where change_num is equal to that read in (1)
> 
>      (3)   return next_id read in (1)

As far as I see, this is a stored function issue not a Java
issue.
I got the exact code of the function from Dave.

    create function next_id_block (
       varchar , integer
     )
     returns bigint
     as '
     DECLARE
       -- Parameters
       name_key          alias for $1 ;
       block_size        alias for $2 ;

       -- Locals
       id_rec            record ;
       num_rows          integer ;
     BEGIN
       -- To avoid a retry-loop, lock the whole table for the
transaction
       lock table idfactory in exclusive mode ;

       -- Read the current value of next_id
       select into id_rec * from idfactory where name = name_key ;

       -- Increment it by block_size
       update idfactory
           set next_id = next_id + block_size,
               change_num = change_num + 1
           where name = name_key and change_num = id_rec.change_num ;

       -- If the update failed, raise an exception
       get diagnostics num_rows = ROW_COUNT ;
       if num_rows != 1 then
         raise exception ''Update failed'' ;
         return -1 ;
       end if ;

       return id_rec.next_id ;
     END ;
     ' language 'plpgsql' ;

The cause is that the stored function uses a common
snapshot throughout the function execution. As I've
complained many times, the current implementaion is
far from intuition and this case seems to show that
it isn't proper at all either.

*lock table* certainly locks idfactory table but the
subsequenct *select* sees the table using the snapshot
taken before the function call. The *update* statement
find the row matching the where clause using the common
snapshot but will find the row was already updated and
the updated row doesn't satisfy the condition any longer.

[In case when we remove the *lock* statement and add a
 *for update* clause to the subsequent *select* statement]

The *select .. for update* statement gets the latest
(may be updated) change_num value. Unfortunately
the subsequent *update* statement has a where clause
containing change_num. The *update* statemnet can't
find the row matching the where clause using the snapshot
taken before the function call.

regards,
Hiroshi Inoue

Wed, 03 Oct 2001 10:36:05 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: LOCK TABLE oddness in PLpgSQL function called via JDBC
At 06:36 PM 10/2/2001, Hiroshi Inoue wrote:
>The cause is that the stored function uses a common
>snapshot throughout the function execution. As I've
>complained many times, the current implementaion is
>far from intuition and this case seems to show that
>it isn't proper at all either.

Bravo! That indeed seems to have been the problem. To solve it, I simply 
moved the LOCK TABLE out of the PLpgSQL function and into the JDBC code. 
While this isn't *ideal* as it leaves the table locked across two JDBC 
calls (the function and the following commit), it achieves the desired 
result (synchronous access to the idfactory table across all clients), and 
as I said, the function won't be called very often. It's far more important 
that it work as expected rather than it work in sub-millisecond time.

To illustrate then what seems to have been occurring:

     Time    Thread A      Thread B
      1      snapshot
      2      lock
      3      read 1, 1
      4      write 11, 2
      5                    snapshot
      6      return 1
      7      commit
      8                    lock
      9                    read 1, 1
     10                    write 11, 2
     11                    FAIL

As long as thread B takes its snapshot any time before the commit at (7), 
its write at (10) will not affect any rows because ...

>The *update* statement
>find the row matching the where clause using the common
>snapshot but will find the row was already updated and
>the updated row doesn't satisfy the condition any longer.

Ouch. So querying for select, update, delete, whatever goes against the 
snapshot to *locate* rows, but then applies the where clause to the *new 
values* not seen in the snapshot? If that's the case, that's extremely 
confusing.

Anyway, many thanks to everyone for keeping me from going totally insane. 
Luckily the other stored procedures we need to write won't require such 
strict access to table data. :)

Peace,
Dave


Tue, 02 Oct 2001 19:37:40 -0700
Dave Harkness <daveh@MEconomy.com>
PROBLEM SOLVED: LOCK TABLE oddness in PLpgSQL function called via
 JDBC