| Subject | Re: [firebird-support] How best to prevent overlapping updates by multiple users? |
|---|---|
| Author | Helen Borrie |
| Post date | 2008-03-06T21:29:24Z |
At 02:44 AM 7/03/2008, you wrote:
Now, the reason why your "users" (but, really, one user's transaction) seems capable of overwriting another's work is in the timing of the post. With Firebird's optimistic row-level locking, the first transaction to POST the change gets the lock. The lock remains until that transaction is either committed or rolled back. Afterwards, another transaction that wanted to change that record can either proceed, or will get an exception - what actually happens depends on the transaction settings.
Of course, "who gets there first", it's not about who *read* the record first, but who *posted a change first*. Merely reading a record doesn't (normally) block others from writing to it. To give the first reader a lock, you would need to impose one or another form of pessimistic lock as close as possible to the point where the user decides to change a record. One way to do this is to perform a "dummy update" as soon as the user selects the record for editing. Another is to pick a record using a selector of some kind and grab its data using a select on that record alone using FOR UPDATE WITH LOCK. (Don't use FOR UPDATE WITH LOCK on multi-row sets!!)
If you're using ANY form of pessimistic locking, make certain that you're not using autocommit transactions. You'll need to wrap the entire operation inside an explicit transaction and prevent the user from leaving it uncommitted once the task is complete.
./heLen
>I'm a bit puzzled as to the best way to achieve the following:I've no idea how you are "locking columns", since Fb's locking is row-level. As soon as one transaction posts a change or deletion to a record, the whole row becomes locked against changes by other transactions.
>
>I have rows which represent documents and they include an "Issued"
>date column and a "Cancelled" date column. A document can have 3 states:
>"Draft" (both date columns are null).
>"Issued" ("Issued" date column is not null."Cancelled" date column is
>null).
>"Cancelled" ("Cancelled" column is not null).
>
>Depending on the document state, other columns may be "locked" and
>updates to them are prohibited. At all times some of the columns must
>remain editable.
>
>What is the best way to cater for the situation where 2 users open the
>same draft document simultaneously and one user changes its state to
>"issued"? This supposedly locks certain columns but the other user
>still sees the document as a "draft" and can still update the "locked"
>columns?
Now, the reason why your "users" (but, really, one user's transaction) seems capable of overwriting another's work is in the timing of the post. With Firebird's optimistic row-level locking, the first transaction to POST the change gets the lock. The lock remains until that transaction is either committed or rolled back. Afterwards, another transaction that wanted to change that record can either proceed, or will get an exception - what actually happens depends on the transaction settings.
Of course, "who gets there first", it's not about who *read* the record first, but who *posted a change first*. Merely reading a record doesn't (normally) block others from writing to it. To give the first reader a lock, you would need to impose one or another form of pessimistic lock as close as possible to the point where the user decides to change a record. One way to do this is to perform a "dummy update" as soon as the user selects the record for editing. Another is to pick a record using a selector of some kind and grab its data using a select on that record alone using FOR UPDATE WITH LOCK. (Don't use FOR UPDATE WITH LOCK on multi-row sets!!)
If you're using ANY form of pessimistic locking, make certain that you're not using autocommit transactions. You'll need to wrap the entire operation inside an explicit transaction and prevent the user from leaving it uncommitted once the task is complete.
>I'd be really grateful for any guidance on this.Well, as Set and Ann have indicated, there does come a time when you have to stop pretending Firebird doesn't have transaction isolation and start using it for its intended purposes. ;-) If I'm not mistaken, you're an IBO user...IBO has strong support for all the highways and byways of transaction management, meaning there's not a lot you have to design yourself in order to get the exact behaviour you want.
./heLen