Re: row-level locking
Markus Wagner wrote:
We found a short description for the "SELECT FOR UPDATE" statement:
"perform exclusive locking of selected rows"Now my question: Is this a good method for our purpose?
I had the same question not too long ago.
It's a great method.
It doesn(t have to be in an explicit transaction as all postgres sql
statements are executed in their own transactions. The row will be
locked until your transaction (implicit or explicit) is commited or
rolled-back.
From a VB application the tricky part will be finding out how to start
an explicit transaction and then committing it.
For example I found out that in Java:
SELECT ... FOR UPDATE;
[do some stuff on the row]
Did not lock the rows as expected since Java automatically "committed"
the implicit transaction around the "SELECT ... FOR UPDATE" statement.
I had to turn off the default transaction commital of the JDBC, execute
my query/statement and *then* explicitly commit the transaction.
VB and ODBC might need something similar.
HTH,
Jean-Christian Imbeault
Import Notes
Reference msg id not found: 200304011336.41155.wagner@imbei.uni-mainz.de
On Tue, 1 Apr 2003, Markus Wagner wrote:
Hi,
we need to prevent write access for a single row of a table for a short time.
We found a short description for the "SELECT FOR UPDATE" statement:
"perform exclusive locking of selected rows"Now my question: Is this a good method for our purpose?
Assuming that we lock our row with
"SELECT FOR UPDATE FROM <table> WHERE <our condition>",
how can we unlock it later?
The syntax is: SELECT * FROM <table> ... FOR UPDATE
(just saving you that error message)
Do we have to put the whole thing in a transaction (which may be tricky from
within a VB-Application...)?
Yes, it locks the selected rows for the duration of the surrounding
transaction. Therefore, you need to be in a transaction when you issue the
SELECT and you can only release the lock by commiting or rolling back.
If you're using 7.3+ you might want to look at the AUTOCOMMIT setting/mode. I
believe that was added for the 7.3 release and it will enable you to avoid
issuing the BEGIN explicitly, although obviously you still need the COMMIT or
ROLLBACK.
Thanks a lot,
Markus
--
Nigel J. Andrews
Import Notes
Reply to msg id not found: 200304011336.41155.wagner@imbei.uni-mainz.de | Resolved by subject fallback