| Subject | Re: [firebird-support] How to lock a record and check locked or not. |
|---|---|
| Author | Helen Borrie |
| Post date | 2003-09-08T05:13:05Z |
At 12:55 PM 8/09/2003 +0900, you wrote:
multiple transactions can read a record simultaneously, even if one or more
transactions has a change in progress. Your transaction has a certain
isolation level that determines whether it is allowed to post a change that
will overwrite a change made by another transaction since your transaction
started.
protection you are seeking: but only at the server.
Your IBX application will not "know" about changes done elsewhere until
your user actually tries to post HER changes. That is because the editing
is being done on a buffered copy of the data, not the data itself. This
can be really annoying for your user, because her work is wasted.
The usual way around this is to employ a "pessimistic locking" technique
from the client app. Simply put, you post a dummy update to the database
record (without committing the transaction) **before** you let the user
take the record into Edit mode. If the dummy update fails, then don't let
the record go into Edit mode.
Assuming your table has a primary key, one way to do this is just to issue
an SQL statement:
update thistable set yourPK=yourPK
where yourPK = :yourPK
Post this statement. If it fails, you know that another transaction has
already either 1) done the same thing with this record and has the lock or
b) another transaction has committed a version of this record that is newer
than the version your user is looking at. If it succeeds, she can go ahead
and edit the record and, provided other applications have the same setup,
they won't be able to "step on" her work.
This is the only way in Firebird 1.0.3 that you can test whether the record
is locked and, if not, to lock it. However, pessimistic locking goes
against the way Firebird is designed to work and shouldn't be used in
situations where it doesn't matter if multiple transactions should happen
to touch the same record.
It would be worth asking at borland.public.delphi.interbasexpress on
news://forums.borland.com, whether there is a version of IBX that
implements pessimistic locking for you (as IBObjects does). It will make
your task much simpler...
heLen
>Hello all,OK, Firebird has what's called "optimistic locking". This means that
>
>I am developping a software whitch store image(Jpeg) with some information
>on a record.
>A program can view a imges with information and edit them. So that it need
>to lock the record while viewing the image to avoid to modify the same
>record form another network cleint program.
>
>Please advise how to lock a record and check a record is locked or not.
multiple transactions can read a record simultaneously, even if one or more
transactions has a change in progress. Your transaction has a certain
isolation level that determines whether it is allowed to post a change that
will overwrite a change made by another transaction since your transaction
started.
>C++Builder 6.0 IBX component.In IBX, the default isolation level is Concurrency, which gives the
>Firebird 1.03
>Windows 2000
protection you are seeking: but only at the server.
Your IBX application will not "know" about changes done elsewhere until
your user actually tries to post HER changes. That is because the editing
is being done on a buffered copy of the data, not the data itself. This
can be really annoying for your user, because her work is wasted.
The usual way around this is to employ a "pessimistic locking" technique
from the client app. Simply put, you post a dummy update to the database
record (without committing the transaction) **before** you let the user
take the record into Edit mode. If the dummy update fails, then don't let
the record go into Edit mode.
Assuming your table has a primary key, one way to do this is just to issue
an SQL statement:
update thistable set yourPK=yourPK
where yourPK = :yourPK
Post this statement. If it fails, you know that another transaction has
already either 1) done the same thing with this record and has the lock or
b) another transaction has committed a version of this record that is newer
than the version your user is looking at. If it succeeds, she can go ahead
and edit the record and, provided other applications have the same setup,
they won't be able to "step on" her work.
This is the only way in Firebird 1.0.3 that you can test whether the record
is locked and, if not, to lock it. However, pessimistic locking goes
against the way Firebird is designed to work and shouldn't be used in
situations where it doesn't matter if multiple transactions should happen
to touch the same record.
It would be worth asking at borland.public.delphi.interbasexpress on
news://forums.borland.com, whether there is a version of IBX that
implements pessimistic locking for you (as IBObjects does). It will make
your task much simpler...
heLen