Hi Peter,

--- In firebird-support@yahoogroups.com, "Peter Ypenburg"
<PeterY@B...> wrote:

> We use a generator to flag a process as locked or not. I will try

and

Ah, I can see your problem already.

Consider this scenario:

User1

1. Increments Generator
2. Gets the number 1 back, so he is "safe"

User 2

1. Increments Generator
2. Gets the number 2 back, but before he can "undo" the increment,
someone trips over the power cable and so the decrement is never run.

User 1

1. Finishes laughing at user 2 for falling over, quits the program
and the Generator is decremented, (only back to value 1 though).

So now your lock is on. I am sure there are other ways to achieve
this but as you can see it is not a safe "gate".

As Michael pointed out (correctly), Generators are transaction
independent. This means that even though User 2's transaction will
roll back, the generator call will NOT.

Here is a better way of achieving your lock. I have used it now for
about 6 months across hundreds of customers and it has NEVER failed
(and you would shake your head if you saw some of the hardware the
poor thing has to run on).

Create a table

ExportLock
(
InUse Char(1)
)

Insert into ExportLock(InUse) values ('F');
Commit;

Now modify your programs

Then instead of calling the generator, run the query. Put it in its
own transaction (Read_Committed) and do NOT commit it.

Update ExportLock set InUse = 'T';

But how can this work you ask?

When User 1 executes this line, the record is locked until the
transaction is committed.

When User 2 attempts to execute this line, they get an exception (if
READ_COMMITTED). Your application can then respond to this exception
with a meaningful error message.

When User 1 has finished, commit or rollback the transaction.

Now, our method is more sophisticated again. We have another table
that stores the current user so that the error message says
that "Peter is currently using this." etc.

Hope that helps

Adam

> 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