Re: [firebird-support] How to limit the number of concurrent users to a database
Fulvio Senore wrote: Do you think that this a reasonable solution, or are there better ones? How about this: Create ON CONNECT database trigger and read number of connected users from MON$ATTACHMENTS. If it's larger than what you want, throw an exception. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard == ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] How to limit the number of concurrent users to a database
On 2012-06-13 18:56, Thomas Steinmaurer wrote: 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. Thomas, Just by the way: do you know a simple way (a stored procedure with elevated rights?) to get *all* active connections from MON$ATTACHMENTS while connected as an ordinary user (not with the RDB$ADMIN role)? I've got a different problem, in which such solution would help a lot (meanwhile, my users connect with RDB$ADMIN, but that causes other problems, e.g. they can still connect to a database in single mode). thanks Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__ ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] How to limit the number of concurrent users to a database
On 2012-06-13 18:56, Thomas Steinmaurer wrote: 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. Thomas, Just by the way: do you know a simple way (a stored procedure with elevated rights?) to get *all* active connections from MON$ATTACHMENTS while connected as an ordinary user (not with the RDB$ADMIN role)? Good one. No, AFAIK there is no way, thus my suggestion with querying MON$ATTACHMENTS in an ON CONNECT trigger only works if connecting with SYSDBA or the database owner and not with an ordinary user. -- With regards, Thomas Steinmaurer http://www.upscene.com/ I've got a different problem, in which such solution would help a lot (meanwhile, my users connect with RDB$ADMIN, but that causes other problems, e.g. they can still connect to a database in single mode). ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] How to limit the number of concurrent users to a database
Signore Fulvio Senore, If the problem really is to limit the simultaneous connections, if after each query, you close the connection, this will by his self assure that the simultaneously connected users will be the minimum. And will bring other benefits, as minimizing net traffic. As normally well planned queries and transactions in well managed servers don´t last more than a second, doesn´t matter how much applications and machines are connected to the db server, they will not stay connected much longer. And so, not many will be connected at the same time - and probablyat most of the time, you will have only one user really connected to the db server. Ask your front-end applications developers, how they programm your applications. Or your software deliverers... Today is not considered a good practice having a connection open for more than the time needed to send a request and get the result back. And for this reason, many paid sql servers send servers by the number of simultaneos connections. Best regards, Roberto Camargo. --- On Thu, 6/14/12, Michael Ludwig mil...@gmx.de wrote: From: Michael Ludwig mil...@gmx.de Subject: Re: [firebird-support] How to limit the number of concurrent users to a database To: firebird-support@yahoogroups.com Date: Thursday, June 14, 2012, 2:05 AM Fulvio Senore schrieb am 13.06.2012 um 22:17 (+0200): My purpose is very simple. The users can install the program on any number of computers connected to a Firebird server, and I want to limit the number of simultaneous connections. Do you control the server? If you don't it'll be difficult to enforce anything anyway. But if you do you might investigate whether it's possible to configure this at the network level, like with iptables on Linux, or whatnot on Windows. Take a look here, I didn't do further checks so I don't know whether it's possible or not: https://www.google.com/search?q=iptables+limit+number+of+connections Michael ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links [Non-text portions of this message have been removed]
Re: [firebird-support] How to limit the number of concurrent users to a database
Michael Ludwig wrote: Fulvio Senore schrieb am 13.06.2012 um 22:17 (+0200): My purpose is very simple. The users can install the program on any number of computers connected to a Firebird server, and I want to limit the number of simultaneous connections. Do you control the server? If you don't it'll be difficult to enforce anything anyway. But if you do you might investigate whether it's possible to configure this at the network level, like with iptables on Linux You use Firebird Classic, you can also limit the number of connections via xinetd: http://www.firebirdfaq.org/faq161/ HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] How to limit the number of concurrent users to a database
Il 14/06/2012 12.36, Thomas Steinmaurer ha scritto: On 2012-06-13 18:56, Thomas Steinmaurer wrote: 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. Thomas, Just by the way: do you know a simple way (a stored procedure with elevated rights?) to get *all* active connections from MON$ATTACHMENTS while connected as an ordinary user (not with the RDB$ADMIN role)? Good one. No, AFAIK there is no way, thus my suggestion with querying MON$ATTACHMENTS in an ON CONNECT trigger only works if connecting with SYSDBA or the database owner and not with an ordinary user. Does MON$ATTACHMENTS list users also for Classic, since Classic uses different processes? Fulvio ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] How to limit the number of concurrent users to a database
Il 14/06/2012 12.36, Thomas Steinmaurer ha scritto: On 2012-06-13 18:56, Thomas Steinmaurer wrote: 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. Thomas, Just by the way: do you know a simple way (a stored procedure with elevated rights?) to get *all* active connections from MON$ATTACHMENTS while connected as an ordinary user (not with the RDB$ADMIN role)? Good one. No, AFAIK there is no way, thus my suggestion with querying MON$ATTACHMENTS in an ON CONNECT trigger only works if connecting with SYSDBA or the database owner and not with an ordinary user. Does MON$ATTACHMENTS list users also for Classic, since Classic uses different processes? Yes. -- With regards, Thomas Steinmaurer http://www.upscene.com/ ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] How to limit the number of concurrent users to a database
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? Thanks in advance. Fulvio Senore ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] How to limit the number of concurrent users to a database
On Wed, Jun 13, 2012 at 11:39 AM, Fulvio Senore mail...@fsoft.it wrote: 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. 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. It really depends on your application profile - there are applications where blocking garbage collection for the duration of a connection would be catastrophic. 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? After a crash, the transaction that took the lock will be declared dead and its lock removed. Good luck, Ann [Non-text portions of this message have been removed]
Re: [firebird-support] How to limit the number of concurrent users to a database
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? In this case, it can take up to 2h until Firebird detects that the connection was broken, and in the meantime, the lock will stay active. This detection time can be shorten by setting the TCPIP parameters in the operating system or (not always recommended) setting the DummyPacketInterval in the fb.conf file. []s Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br
Re: [firebird-support] How to limit the number of concurrent users to a database
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 ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] How to limit the number of concurrent users to a database
Em 13/6/2012 14:56, Thomas Steinmaurer escreveu: 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 Besides the cases you pointed out, there is the case of client lost conection. But I think that this is the easiest way to do it... The OP could tell us some more info like: Do you want to count the simultaneous connections or the number of users ? (ex. the costumer could have 10 valid users but are allowed to use just 5 at a time) see you ! ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] How to limit the number of concurrent users to a database
Besides the cases you pointed out, there is the case of client lost conection. But I think that this is the easiest way to do it... The OP could tell us some more info like: Do you want to count the simultaneous connections or the number of users ? (ex. the costumer could have 10 valid users but are allowed to use just 5 at a time) My purpose is very simple. The users can install the program on any number of computers connected to a Firebird server, and I want to limit the number of simultaneous connections. So they could install it on 5 computers but, if they payed for 2 connections, they will be able to use the program only on 2 computers at the same time. Fulvio ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] How to limit the number of concurrent users to a database
Em 13/6/2012 17:17, Fulvio Senore escreveu: Besides the cases you pointed out, there is the case of client lost conection. But I think that this is the easiest way to do it... The OP could tell us some more info like: Do you want to count the simultaneous connections or the number of users ? (ex. the costumer could have 10 valid users but are allowed to use just 5 at a time) My purpose is very simple. The users can install the program on any number of computers connected to a Firebird server, and I want to limit the number of simultaneous connections. So they could install it on 5 computers but, if they payed for 2 connections, they will be able to use the program only on 2 computers at the same time. Fulvio I would go for the Thomas's sugestion to use on connect trigger. *If* you identify that lost connections (ungracefully client app exit) is a concern, you could employ some logic into your app to update the last time used in a table, and ignore any connection that is not used for 5 minutes (or any time you think is reasonable) ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] How to limit the number of concurrent users to a database
Why control it in the server side?if your customer can connect to the database as sysdba, can remove the trigger or change the logic in the connect event. If You do it on the client side, after connect, do a select of mon$attachments and if it is greater than the licensed concurrent connections, then You can disconnect the user or show the connection list and let the user drop an existing connection. Jesus
Re: [firebird-support] How to limit the number of concurrent users to a database
Em 13/6/2012 18:03, Jesús García escreveu: Why control it in the server side?if your customer can connect to the database as sysdba, can remove the trigger or change the logic in the connect event. If You do it on the client side, after connect, do a select of mon$attachments and if it is greater than the licensed concurrent connections, then You can disconnect the user or show the connection list and let the user drop an existing connection. Jesus He could use the approachs combined. I think that just relying on MON$CONNECTIONS (or any table populate upon connect/disconnect) is prone to error due to abnormal app termination. If I would implement it I would have a last time used that is updated by the application, a timer or any other periodical interval (open query, open form, etc.). And consider any connection with a last time used greater than some interval as a dead connection. see you !
Re: [firebird-support] How to limit the number of concurrent users to a database
Fulvio Senore schrieb am 13.06.2012 um 22:17 (+0200): My purpose is very simple. The users can install the program on any number of computers connected to a Firebird server, and I want to limit the number of simultaneous connections. Do you control the server? If you don't it'll be difficult to enforce anything anyway. But if you do you might investigate whether it's possible to configure this at the network level, like with iptables on Linux, or whatnot on Windows. Take a look here, I didn't do further checks so I don't know whether it's possible or not: https://www.google.com/search?q=iptables+limit+number+of+connections Michael
