Firebird record locking

On an app with about 20 users running on Win2003 Server and Terminal
Server very rarely (but once is too often) I get a record locking
problem - a duplicate order number is assigned to 2 users. I think it
might be related to BDE local share not being set to true on all their
new PCs and I've asked the client to check them all again.

But I'd like to prevent it from happening again for any reason  Is
file or record locking more robust and controllable with Firebird

Thanks

In article <sm06m31loonjlfj5vt9gb8rg4v89tsre5p@4ax.com>, 
fpolan@pcassist.on.ca says...
> On an app with about 20 users running on Win2003 Server and Terminal
> Server very rarely (but once is too often) I get a record locking
> problem - a duplicate order number is assigned to 2 users. I think it
> might be related to BDE local share not being set to true on all their
> new PCs and I've asked the client to check them all again.
> 
> But I'd like to prevent it from happening again for any reason  Is
> file or record locking more robust and controllable with Firebird
> 
> Thanks
> 
> Frank Polan
> 

Frank,

I'm still learning this stuff, but as I understand it, in the world of 
backend databases like Firebird, you make every effort to avoid locking 
a table or a row and when you do lock something you keep it as brief as 
possible. Commands like UPDATE and DELETE within or without Transactions 
(commit/rollback) are preferred over manual locking and updating of a 
row or rows.

Are you using AutoInc or your own table for generating the Order Number? 
Firebird has something called a GENERATOR which plays the same role as 
the AutoInc. You can continue to use your own table. If I recall 
correctly, Firebird will throw an error if two people read from a table 
and one starts to update the row. The second person who tries to update 
will get an error since their row is out-of-date. So your code would 
have to handle this situation and reread from your table (ie. try 
again).

All backends are more robust in this type of thing over dBASE tables.

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.

Geoff

I'm using my own table - the number has to be available before the
order is committed.
The sequence is - start an order - lock the Number table - get a
number - unlock table - process order. So the lock is practically
instantaneous.

As I was writing this response I just thought of something else to
check.  I'll be back later

Thanks

Frank Polan

On Sat, 15 Dec 2007 01:01:12 -0500, Geoff Wass [dBVIPS]
<gswassREMOVE_ME@attglobal.net> wrote:

>In article <sm06m31loonjlfj5vt9gb8rg4v89tsre5p@4ax.com>, 
>fpolan@pcassist.on.ca says...
>> On an app with about 20 users running on Win2003 Server and Terminal
>> Server very rarely (but once is too often) I get a record locking
>> problem - a duplicate order number is assigned to 2 users. I think it
>> might be related to BDE local share not being set to true on all their
>> new PCs and I've asked the client to check them all again.
>> 
>> But I'd like to prevent it from happening again for any reason  Is
>> file or record locking more robust and controllable with Firebird
>> 
>> Thanks
>> 
>> Frank Polan
>> 
>
>Frank,
>
>I'm still learning this stuff, but as I understand it, in the world of 
>backend databases like Firebird, you make every effort to avoid locking 
>a table or a row and when you do lock something you keep it as brief as 
>possible. Commands like UPDATE and DELETE within or without Transactions 
>(commit/rollback) are preferred over manual locking and updating of a 
>row or rows.
>
>Are you using AutoInc or your own table for generating the Order Number? 
>Firebird has something called a GENERATOR which plays the same role as 
>the AutoInc. You can continue to use your own table. If I recall 
>correctly, Firebird will throw an error if two people read from a table 
>and one starts to update the row. The second person who tries to update 
>will get an error since their row is out-of-date. So your code would 
>have to handle this situation and reread from your table (ie. try 
>again).
>
>All backends are more robust in this type of thing over dBASE tables.

I'll be taking a look at Firebird
Thanks for the information

Frank Polan

On Mon, 17 Dec 2007 14:07:32 +0100, Lysander <nobody@nowhere.de>
wrote:

>Lysander schrieb:
>
>> If user 1 "takes" one number you should update that marker
field.
>> If user 2 opened the same query later, but yet before user 1 took the 
>> number, user 2 will get a special error message.
>
>Forgot to add the (for me) obvious:
>You must then of course do a
>"Select numberfield from numbers where marker is null"
>
>To get only the numbers which are still free at the moment you open the 
>table. As written before, if 2 users open the table for reading and 
>therefore get the same numbers as "free" and one takes a number,
the 2nd 
>user will get a special error message.
>

Frank J. Polan schrieb:

> But I'd like to prevent it from happening again for any reason  Is
> file or record locking more robust and controllable with Firebird

Firebird - like any REAL backend database - operates on transactions.
This is by far more secure than the record/table locking in DBFs.

If you have your own predifined numbers table and you need your users to 
"cross out" one after another, you would best add a field
"used" 
(smallint, 0 = no, 1 = yes) or even "used by" (user-id) or "used
on" (date).

If user 1 "takes" one number you should update that marker field.
If user 2 opened the same query later, but yet before user 1 took the 
number, user 2 will get a special error message.

You can "try/catch" that message out, and in the background 
automatically take the next free number.


By all means: NEVER USE ROW-LOCKING in a SQL-database. Firebird supports 
that, but it's for rare special cases, and the DBA should definitely