Actually a "select * from table with lock" is the simplest, I think

----- Original Message -----
From: "Kevin Stanton" <Kevin.Stanton@...>
To: <firebird-support@yahoogroups.com>
Sent: Thursday, May 12, 2005 2:29 PM
Subject: RE: [firebird-support] Getting a unique value from a generator


> Would it not be simpler to have a control table to do this rather than a
> generator?
>
> create table MyControlTable (
> id integer not null, // primary key via generator
> process varchar(x), // process name or id
> locked char(1), // Y or N
> lockedby varchar(x), // user name or id who has the lock
> constraint pk_MyControlTable primary key (id))
>
> Then just check for a Y or N in the locked column.
>
> Generators to me would be a hassle resetting in a large environment.
>
> Kevin
>
>
>
> _____
>
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Peter Ypenburg
> Sent: Thursday, May 12, 2005 1:07 PM
> To: firebird-support@yahoogroups.com
> Subject: RE: [firebird-support] Getting a unique value from a generator
>
>
> We use a generator to flag a process as locked or not. I will try and
> explain,
>
> - The value of the generator starts of as zero.
> - The first user to execute the SQL SELECT GEN_ID(EXPORTLOCK_SEQ, 1) FROM
> RDB$DATABASE and gets a value of one is deemed to have the lock.
> - While user 1 has the lock other users run the same SQL and if they get a
> value bigger than one they do not have the lock and then run the following
> SQL to undo their effort to get the lock i.e. SELECT
> GEN_ID(EXPORTLOCK_SEQ,
> -1) FROM RDB$DATABASE.
> - As soon as user 1 runs his SELECT GEN_ID(EXPORTLOCK_SEQ, -1) FROM
> RDB$DATABASE and all the other users have undone their locks the value of
> the generator should be back at zero.
> - The next user to run SELECT GEN_ID(EXPORTLOCK_SEQ, 1) FROM RDB$DATABASE
> will get a value of 1 and he is deemed to have the lock again.
>
> And so the cycle continues. The client has about 150 users trying to get
> the
> lock at a point in time. Somewhere along the line at the client's site (we
> have not been able to figure out why) this gets broken and the lock stays
> on. In our test environment we have not been able to break the logic, the
> code is pretty well protected with try finally's and works.
>
> The client does run our software using Citrix terminals. Not sure if this
> is
> a possible cause although we have already had them rebuild the Citrix
> environment and yet this still happens.
>
> Are there any flaws in the logic above?
>
>
> Peter Ypenburg
> AGM: Product Development
> Barloworld Optimus
> Tel +2711 286 1587 Fax +2711 286 1630 Cell +2782 789 9167
> www.barloworldoptimus.com
>
> Disclaimer
>
> The information transmitted is the property of Barloworld Optimus Pty
> (Ltd)
> and is intended only for the person or entity to which it is addressed and
> may contain confidential and/or privileged material. Statements and
> opinions
> expressed in this e-mail may not represent those of the company. Any
> review,
> retransmission, dissemination and other use of, or taking of any action in
> reliance upon, this information by persons or entities other than the
> intended recipient is prohibited. If you received this in error, please
> contact the sender immediately and delete the material from any computer.
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Mitchell Peek
> Sent: Thursday, May 12, 2005 9:40 PM
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] Getting a unique value from a generator
>
> helen or someone will reply later and tell me how wrong I am ;), but....
>
> First, it isn't really possible for 2 users to execute the statement at
> the *exact* same time, one has to come before the other... but anyway....
>
> Generators operate outside of user transactions. Test it. A rollback
> leaves the generator as it was whether you rollback or commit.
> Therefore, it isn't possible for the same value to be returned if you
> assume they are the only two statements going on. However, if there
> were a third user such that the execution occurred in this manner....
>
> user 1
>
> SELECT GEN_ID(EXPORTLOCK_SEQ, -1)
> FROM RDB$DATABASE;
>
> user 2
>
> SELECT GEN_ID(EXPORTLOCK_SEQ, 1)
> FROM RDB$DATABASE
>
> user 3
>
> SELECT GEN_ID(EXPORTLOCK_SEQ, -1)
> FROM RDB$DATABASE
>
>
> then users 1 and 3 will get the same value.
>
> Is this a possibility?
>
>
>
>
>
>
> Peter Ypenburg wrote:
>
>>Hi There,
>>
>>Is it possible for two users who run the following SQL at the exact same
>>time (each in their own transaction) to get the same value back from a
>>generator. The SQL is as follows:
>>
>>SELECT GEN_ID(EXPORTLOCK_SEQ, -1)
>>FROM RDB$DATABASE
>>
>>Thanks.
>>
>>Peter Ypenburg
>>AGM: Product Development
>>Barloworld Optimus
>>Tel +2711 286 1587 Fax +2711 286 1630 Cell +2782 789 9167
>>www.barloworldoptimus.com
>>
>>Disclaimer
>>
>>The information transmitted is the property of Barloworld Optimus Pty
>>(Ltd)
>>and is intended only for the person or entity to which it is addressed and
>>may contain confidential and/or privileged material. Statements and
> opinions
>>expressed in this e-mail may not represent those of the company. Any
> review,
>>retransmission, dissemination and other use of, or taking of any action in
>>reliance upon, this information by persons or entities other than the
>>intended recipient is prohibited. If you received this in error, please
>>contact the sender immediately and delete the material from any computer.
>>
>>
>>
>>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>>Visit http://firebird.sourceforge.net 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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net 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
>
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net 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/
>
>
> * To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
> <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
> <http://docs.yahoo.com/info/terms/> .
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net 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
>
>
>
>
>
>