Re: simultaneous use of JDBC and libpq

Richard Huxton wrote:
> alltest1 wrote:
>
>>Hi,
>>
>>I am wondering if it is thread-safe to use both JDBC and libpq
>>simultaneously.
>>
>>On a Linux, JDBC is used by Tomcat and libpq is used by a client
>>software written in C language. So JDBC and libpq are used by two
>>different programs.
>>If the same row in the same table is updated (update SQL command)
>>through libpq and
>>read (select SQL command) by JDBC, then would it cause a thread problem?
>>I am not using any transaction, and using just select, update, and
>>sometimes insert.
>
>
> There are no threads involved here - Tomcat/client-app will be different
> processes, and both have their own connection to the database.
>
> You might need to consider concurrency issues within the database
> though, e.g. assume your bank account has £100 and TOMCAT is debiting
> £10 while the other app is crediting £15
>
> TOMCAT: SELECT amount FROM bank_accounts WHERE acct_id = 1;
> OTHER:  SELECT amount FROM bank_accounts WHERE acct_id = 1;
> TOMCAT: UPDATE bank_accounts SET amount=90  WHERE acct_id = 1;
> OTHER:  UPDATE bank_accounts SET amount=115 WHERE acct_id = 1;
>
> Oops - not £105 at all. To solve this, you need to lock the table or the
> row(s) in question, e.g.
>   SELECT FOR UPDATE ... WHERE acct_id = 1;]#

Ehm, isn't:

UPDATE bank_accounts SET amount=amout-10 WHERE acct_id = 1;
UPDATE bank_accounts SET amount=amout+15 WHERE acct_id = 1;

just fine (in this case)? No SELECT [FOR UPDATE] needed.

The answer to the original question is that there's no difference
(server side) between a query from libpq and a query from JDBC
(which possibly is uses libpq internally). All clients are equal.
As Mr. Huxton pointed out, you've got concurrency issues, but _any_
application is likely to have to deal with them, even if clients
are using libpq only, or JDBC only.

.TM.
--
      ____/  ____/   /
     /      /       /            Marco Colombo
    ___/  ___  /   /              Technical Manager
   /          /   /             ESI s.r.l.
 _____/ _____/  _/               Colombo@ESI.it