Re: row-level locking

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