Re: JDBC Transactions

2010/11/1 Jonathan Tripathy <jonnyt(at)abpni(dot)co(dot)uk>:
>
> On 01/11/10 18:08, Andy Colson wrote:
>>
>> On 11/1/2010 12:37 PM, Jonathan Tripathy wrote:
>>>
>>> Hi Everyone,
>>>
>>> I'm trying to create a server for a database system which will be used
>>> by multiple clients. Of course, table locking is very important. Reading
>>> the Postgresql docs, locking occurs on a transaction-by-transaction
>>> basis.
>>>
>>> In our java code, we are doing this:
>>>
>>> //Start Code Block
>>>
>>> Connection con = "..."
>>> con.setAutoComitt(false);
>>>
>>> //Insert SQL here to lock table
>>>
>>> String qry1 = "..."
>>> pst1 = con.prepareStatement(qry1)
>>> //Insert code here to add values to prepared statement pst1
>>> pst1.executequery();
>>>
>>> String qry2 = "..."
>>> pst2 = con.prepareStatement(qry2)
>>> //Insert code here to add values to prepared statement pst2
>>> pst2.executequery();
>>>
>>> con.comitt();
>>>
>>> //End Code Block
>>>
>>> My question is, would the above block of code be classed as a single
>>> transaction, and would the locking work correctly?
>>>
>>> Thanks
>>>
>>> Jonny
>>>
>>>
>>
>> Table locking is very bad for concurrent access.  When a table is locked,
>> its one user at a time.
>>
>> PG usually does not need any locks at all.  As long as you use
>> transactions as they were meant to be used (as an atomic operation), things
>> usually work really well, with no locking at all.  You could read up on MVCC
>> is you were interested.
>>
>> Without knowing what sql you are running, I can _totally guarantee_ it'll
>> work perfectly with NO table locking.  :-)
>>
>> -Andy
>
> Hi Andy,
>
> Thanks for your reply. Would the above code be classed as a single
> transaction then?

Yes, assuming there's no explicit transaction control
(COMMIT/ROLLBACK/END) in your queries.

> And if so, I could just simple leave out the line which
> says "//Insert SQL here to lock table"?

In PostgreSQL, locking is done automatically depending on actual
isolation level and SQL queries.
You can use explicit locking but most of the time it's not needed.

--
Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/