At 12:07 AM 13/06/2006, you wrote:

>Thank you for all advises.
>
> > Perhaps you don't understand that pessimistic locking is unnatural to
> > the way Firebird deals with transaction isolation. It has optimistic
> > row-level locking on all reads. This means it does not lock anything
> > until a request arrives to change a row. At that point, the changed
> > row becomes unavailable for writes by any other transaction; but all
> > of the unchanged rows can be accessed by other transactions.
>
>If this true, why an error has occured when I've used a same SELECT
>with "WITH LOCK" from other transaction?

Well, if one transaction has a SELECT WITH LOCK on a set of rows, it
will cause each row, as it is fetched from the server's buffer to the
client, to be locked. The lock is not released until the transaction
ends. So, at any moment, you will have x fetched rows and y rows
remaining in the buffer. All of the x rows are locked (even if you
do not update them) and all of the y rows remain available for other
transactions to update. You will get a lock conflict in the first
transaction if another transaction has, meanwhile, updated a row that
is still waiting in your buffer, and your transaction attempts to
fetch it - since two transactions cannot have locks on the same record.

>what does it mean about "it does not lock anything until a request
>arrives to change a row"?

That is what "optimistic row level locking" means. If two
transactions are looking at the same row and txn A posts an update to
that row, txn gets a lock on that row and thus the other transaction
will get a lock conflict if it then decides to request an update to that row.

>when Firebird will know the row is needed to change?

Firebird will know of the change when the change request is submitted
by the application. If there are no conflicts, the request will be
granted and the row will remain locked until the transaction is
either committed or rolled back.

> > You really must take notice of the warnings in the release notes
> > about using WITH LOCK and never use it for SELECTs that return
>multiple rows.
>
>I've read this, it sound like WITH LOCK is useless. what's is the main
>idea of Firebird's explicit lock? and it was designed for what?

It was designed for experts who have a genuine need to pre-empt
changes being done to a row whilst the application has that row
"selected", e.g. for an edit operation, *before* requesting a change
to it. And you are right, there is not much use for it in Firebird
in the general case.

An example of where it might be used is in a ticket-issuing
application. If User A has selected Ticket 12345 and is busy
entering the issuing details for that ticket, she doesn't want User B
to come along and issue the same ticket to somebody else.

So one would write an application that fetches the record for the
next available ticket (one row) WITH LOCK and the requirement is satisfied.

./heLen