I connect to the PG database using a java front-end application and a
poolmanager on the backend.
All sessions connect as user "webuser" and all access is via views for
SELECT and for UPDATE (there is no direct access to the tables).
I want to use SELECT FOR UPDATE on these views and I'm not clear about the
following issues:
1. Since all sesions connect as webuser, how does SELECT FOR UPDATE work?
Does it recognize different connections, or it just sees a single user (so
the is no locking among connections)?
2. SELECT FOR UPDATE in views locks only the views, or the undelying tables
as well? If two physical users, using two different connections as "webuser"
attempt to update the same table record via two different update views, is
SELECT FOR UPDATE on the views safe?
3. I have situations in which I start a transaction with SELECT FOR UPDATE
and I may finish it with no update at all or even a DELETE (depending on
what's found out within the transaction). In this situation, what happens to
the initial SELECT FOR UPDATE when I commit or rollback the transaction? Is
the lock released or not?