--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

>
> A similar idea. Use a separate transaction for the '# of Users

control' and never commit the update (same way as you use pessimistic
locking).

This would block OAT and possibly cause performance problems. Such an
approach may be possible if you were to manage it in a secondary
thread (with its own connection/transaction) that you did actually
commit and reasonably frequently (say every 20 minutes).

Our approach is slightly different. (We don't use it as a license
count but I suppose we could if we really wanted).

The client-server part of our application has a connection that
persists for the entire time of the session. We map the logged in user
(application authenticated user not firebird user) to the
CURRENT_CONNECTION. Our table looks like the following:

CONNECTIONMAP
(
CURRENTCONNECTION
[other application session specific fields]
REFRESHEDTIME
);

RefreshedTime is indexed.

After the user has been authenticated by our application, it does the
following:

1. Purge table by deleting any record from connectionmap with a
RefreshedTime before CURRENT_TIMESTAMP - (15 minutes), ignoring any
lock conflicts etc. This is done in a stored procedure for select
statement, so that if one record is locked, it still tries the others.

2. Insert a new record into the table with the connection number of
the application connection, and commit. A trigger is in place before
insert or update to set the refreshedtime=CURRENT_TIMESTAMP.

3. Create a secondary thread. This secondary thread wakes up every 10
minutes, establishes a connection to the database, and updates the
correct connectionmap record (which sets the refreshedtime).

This process is from my experience seamless. If a client disconnects
ungracefully, their connectionmap record will be considered as old,
and the next time someone logs in, they will remove it.

I should point out that we use this for auditing purposes (so we can
within triggers determine who initiated a specific change). We don't
use this to count logins for licensing reasons. If you are using such
a mechanism for restricting user counts, it is in theory possible that
two users could simultaneously login with only one license remaining,
and because of transaction isolation they would both succeed. But
certainly you have covered 99.9999% of cases.

Of course both this mechanism, and any others that have been pointed
out can be compromised if a user has access to the fdb file.

Adam