> I have a problem and I think that it is rather common: I am about to
> release a new program that will use a Firebird database and I am
> planning to sell the program at different prices for a different number
> of concurrent users.
> The program will be more expensive if the users want more concurrent
> connections.
>
> So I need a way to limit the number of concurrent users to the database:
> I searched the list history but I have found only a few (and rather old)
> messages about this topic.
>
> I am experimenting with a solution and I would like to receive comments
> about it, just to know if it is a valid one or not.
>
> I have created a custom table, with as many rows as the maximum expected
> number of users. For my program this number will be very small. If the
> user wants a lot of connections there is an unlimited version.
> Each row contains an ID (primary key) and a dummy field.
>
> When a program tries to connect to the database it issues a "SELECT ...
> WITH LOCK" statement in a no-wait transaction, trying to lock the first
> row. If it gets a lock error the row is locked by another user so the
> program tries to lock the second row, and so on.
> In this way it should be easy to count the current number of users and
> the program can know if the user count limit has been reached or not.
> If the program can lock a row it keeps it locked until it terminates.
>
> This solution has the disadvantage of blocking garbage collection so
> each program could commit the locking transaction from time to time.
> This should solve the garbage collection problem.
>
> If a program crashes I suppose that the row will remain locked from some
> time, but I don't know how long it will remain locked. Does anybody know
> this locking time?
>
> Do you think that this a reasonable solution, or are there better ones?

Not bullet-proof, but you could write an ON CONNECT database trigger and
throw an exception if you want to abort the connection establishment.
The condition could be queried e.g. by a COUNT(*) on the MON$ATTACHMENTS
monitoring table or whatever you want to be included in the condition.

Not bullet-proof in a way, that database trigger can be temporarily
disabled at session level or deactivated/dropped when connecting with
the database owner via a tool like isql etc. where you can issue a DROP
TRIGGER statement.


Regards,
Thomas