| Subject | Re: How to limit the number of concurrent users to a database |
|---|---|
| Author | technisoft2005 |
| Post date | 2012-06-14T05:43:44Z |
--- In firebird-support@yahoogroups.com, Fulvio Senore <mailing@...> wrote:
A table with an COMPUTER_NAME (Windows' Computer Name)and a IN_USE column. Logging into the program sets IN_USE = 1, exiting resets IN_USE = 0. To check the number of user I run a SELECT COUNT(*) FROM TABLE WHERE IN_USE = 1
Because we commit these table updates there is no long-term locking.
We have to deal with 3 problems.
1 Crashing client. This is resolved by some logic which allows a login in all cases if COMPUTER_NAME = WindowsMachineName
2 Crashing server or network. On loading the program it checks how many connection there are to the database. If there is only one connection and this connection is itself, the program runs a cleanup routine setting IN_USE of all records (except its own) to 0.
On all systems this happens at least once a day, i.e. there are no 24/7 systems out there.
3 Replacing a faulty computer. This must be handled as case 2
This works fine on an ordinary Windows network because Windows ensures that Machine names are unique.
On a Terminal Server system one has to have a mechanism to artificially create unique machine names. If different computer are set to the same name the logic will prevent simultaneous login, so the uniqueness is ensured at login level rather than OS level.
The login/logout logic is handled by a front end which then forks to other modules.
It is of course possible to defeat this by having another program resetting the IN_USE flag but so far no one has made an attempt at doing that. There are ways one could make it safer, possibly by checking the IP addresses.
Peter
>I have solved it in this way.
> 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.
A table with an COMPUTER_NAME (Windows' Computer Name)and a IN_USE column. Logging into the program sets IN_USE = 1, exiting resets IN_USE = 0. To check the number of user I run a SELECT COUNT(*) FROM TABLE WHERE IN_USE = 1
Because we commit these table updates there is no long-term locking.
We have to deal with 3 problems.
1 Crashing client. This is resolved by some logic which allows a login in all cases if COMPUTER_NAME = WindowsMachineName
2 Crashing server or network. On loading the program it checks how many connection there are to the database. If there is only one connection and this connection is itself, the program runs a cleanup routine setting IN_USE of all records (except its own) to 0.
On all systems this happens at least once a day, i.e. there are no 24/7 systems out there.
3 Replacing a faulty computer. This must be handled as case 2
This works fine on an ordinary Windows network because Windows ensures that Machine names are unique.
On a Terminal Server system one has to have a mechanism to artificially create unique machine names. If different computer are set to the same name the logic will prevent simultaneous login, so the uniqueness is ensured at login level rather than OS level.
The login/logout logic is handled by a front end which then forks to other modules.
It is of course possible to defeat this by having another program resetting the IN_USE flag but so far no one has made an attempt at doing that. There are ways one could make it safer, possibly by checking the IP addresses.
Peter