Adam wrote:

> Hi Stefan,
>
> Something about this post was troubling me, and it has just clicked.
>
> If I understand you, your logic is something like the following. You
> do the following before inserting for example ID 5:
> e is no delete
> select 1
> from tableA
> where ID = 5
>
> Then if 1 is not returned, you assume it is safe to insert.
>
> insert into TableA (id, field1, field2) values (5, 6, 7);
>
> This does not work because the query is run from within the context of
> a transaction. That means the following could happen:
>
> Start Tx1
> Start Tx2
> Tx2: insert ID 5
> Tx1: check if ID 5 exists --> no it doesn't because Tx2 hasn't committed
> Tx1: insert ID 5

I will stop there as the rest is not relevant.
YOUR example shows a generator?
The generator will NEVER supply the same number to two different
transactions, you would always get 5 and 6.

What you need is something unique in the incoming data to control the
insert ( In my case NINumber - XX123456X for anybody outside UK ). If
the NINumber exists I update the existing record, otherwise I call the
generator and get a locally unique number for the record key. Yes I
could use the NINumber as the key, but the advantage of using the
generator is a tidier index, and a count of the total number of records
stored ( there is no delete available ;) ) The insert is 'clean' so the
checks before inserting the new NINumber ensure that the exception can't
happen.

So back to the situation above - every time an insert happens you could
commit the insert, and handle the clash if two records with the same
NINumber come in but that can't happen in my case - so waiting until the
batch is processed is fine. Alternatively - you only use one process to
handle inserting data. In my case the latter is used as well, I queue
data to a 'cache' table when there is no match found, and in a
background process update that data into the main unique table. That
method is also useful when you want to keep things tidy where everyone
can READ the main table, but only the background task can write to it.
Some actions check the cache table as well but that is not essential as
processing of the cache picks up the extra details, such as new duplicates.

It depends on your business model just what method will work, and the
nice thing I have always found with Firebird - it does not DICTATE how
you do things, so you CAN choose a suitable method. It is just a matter
of establishing what works best in YOUR situation. I think people fail
mainly when they try and 'Do things the same as X' rather than actually
addressing the problem. I just write to a record that staffx is editing
it and commit that change - then everybody else see the 'editing' flag
until staffx finishes - the supervisor gets a warning if staffx is
editing for more that 5 or so minutes so any problems can be cleared -
no need to lock records at all :) . In this case the write of the 'edit'
flag also adds the new unique record, before the data is processed, and
the second transaction should see the editing flag on the new record so
further preventing the duplicate situation.

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services