Thank you, very much.

This is very helpful. I will look at the MON$Statements, but as you say it
may not be advised to go that way anyway. My application is a very small
database with a few users. There will not be a great deal of transactions
at a time. But I am interested in learning much more about Firebird, as I
would like to use it in other applications. So I would like to learn the
"right" way to do things. Thank you again.

On Fri, Nov 7, 2008 at 5:07 PM, Helen Borrie <helebor@...> wrote:

> At 02:26 AM 8/11/2008, you wrote:
> >Thank you Helen for your response.
> >
> >I apologize for my question being so vague, but I was trying not to
> violate
> >the rules of this list against being too application development platform
> >specific. The database control referred to is a special control which
> reads
> >and writes to a Firebird database from a Delphi application. When its
> >lockmode property is set to Immediate, if one user has a row in a table
> >"selected" another user can't edit it. A message is generated which I can
> >trap. It's not the whole table, just the row.
> >
> >I thought that if the control was calling some Firebird Select or Update
> >statement (like you mention below) under the covers, which I think it must
> >be doing,
>
> Your dataset component would be doing this one of two ways, which might
> also depend on the version of Firebird that the component supports. Because
> it is Delphi, it is most likely that it achieves the targeted row lock by
> trying to post a dummy update as soon as the application puts the row into
> Edit or Delete mode. If the attempt succeeds, the dummy update makes the row
> unavailable for other transactions to update (even a dummy update) and the
> user owning the second transaction will get an exception.
>
> Firebird 1.5 and later also support a pessimistic *set* lock, whereby each
> row in the set is locked (or refused a lock) as the individual row is
> fetched from the server. This requires the SELECT statement to be modified
> to include a WITH LOCK clause. It's fairly unlikely anyone would implement
> this in a dataset component, though, since it should never be targeted at a
> set larger than one or a few rows.
>
> >then it might be possible to query a system table to see if a user
> >had a lock on that table and maybe even get the user's user name.
>
> In Firebird 2.1, it should be possible to track back from MON$Statements.
> You will need to study the release notes on this. The other side of that is
> that it is a lot of overhead if you want to do that for every statement all
> the time. All you would find out (providing the timing was right) is that
> certain users had "some row in Table X" locked. Since rows with pending
> updates are always locked, regardless of what the application did to make it
> that way, it's not necessarily going to find your offenders.
>
> >I looked at the system tables but didn't see anything that might have that
> >information.
>
> Correct. The monitoring tables *are* system tables of a kind but they don't
> hold permanent data.
>
> >But I don't really know what the control is doing (I don't have the source
> code),
>
> Nevertheless, you owe it to yourself to understand what your components are
> actually doing. Surely there must be technical documentation available that
> explains the effects of the properties and methods of your components. Most
> component suites for Delphi also have their own forums where you can find
> out stuff that isn't documented.
>
> >so if my question does not have enough to go on in order to give an
> >answer to it, I understand. Also, it could be the control is somehow
> >maintaining its own lock mechanism on top of Firebird, I really can't say.
>
> Before Firebird 1.5, the client-side dummy update was the only way to
> defeat optimistic locking. SELECT...WITH LOCK was introduced in v.1.5 and it
> has enough caveats for the typical Delphi way of doing things to make it a
> must-avoid. Pessimistic locking has a particular use when serialisation is a
> requirement; unfortunately, in Delphi applications, it is often used for the
> wrong reasons and without the necessary accompanying treatments to ensure
> that the system doesn't get blocked up by careless users.
>
> ./heLen
>
>
>


[Non-text portions of this message have been removed]