Skip site navigation (1) Skip section navigation (2)

Re: LOCK TABLE oddness in PLpgSQL function called via JDBC

From: Barry Lind <barry(at)xythos(dot)com>
To: Dave Harkness <daveh(at)MEconomy(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: LOCK TABLE oddness in PLpgSQL function called via JDBC
Date: 2001-10-03 01:13:14
Message-ID: 3BBA662A.9010801@xythos.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackerspgsql-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
> 



In response to

pgsql-hackers by date

Next:From: Hiroshi InoueDate: 2001-10-03 01:36:05
Subject: Re: LOCK TABLE oddness in PLpgSQL function called via JDBC
Previous:From: Tatsuo IshiiDate: 2001-10-03 01:11:03
Subject: Re: Unicode combining characters

pgsql-jdbc by date

Next:From: Hiroshi InoueDate: 2001-10-03 01:36:05
Subject: Re: LOCK TABLE oddness in PLpgSQL function called via JDBC
Previous:From: Dave CramerDate: 2001-10-03 01:11:49
Subject: Re: driver fails to handle strings in query statements properly

pgsql-general by date

Next:From: Mark HigginsDate: 2001-10-03 01:26:06
Subject: Column existence - how to check?
Previous:From: Barry LindDate: 2001-10-03 01:03:26
Subject: Re: LOCK TABLE oddness in PLpgSQL function called via JDBC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group