Обсуждение: row-level locking
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? Do we have to put the whole thing in a transaction (which may be tricky from within a VB-Application...)? Thanks a lot, Markus
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
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
Hi all, thanks a lot for these *quick* answers! Markus On Tuesday 01 April 2003 13:36, Markus Wagner wrote: > Now my question: Is this a good method for our purpose?
All you have to do is to call the BeginTrans method of the Connecion object of ADO in VB using ODBC. While in transaction mode you can lock as many rows as you want using "Select .... For Update" query but remember that if the row is already locked by some other user/client, all the rows locked by your transaction will be automatically rolledback. To release the locked rows you can either call the RollbackTrans or CommitTrans method of the Connection object. --- Markus Wagner <wagner@imbei.uni-mainz.de> 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? > > Do we have to put the whole thing in a transaction > (which may be tricky from > within a VB-Application...)? > > Thanks a lot, > Markus > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html __________________________________________________ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer