--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:

>
> I saw your answer in 'deadlock' in forum, so I hope, you can help

me:)

> I am working on site statistics application in php and firebird
> database. But I still have deadlock message, and I don't know how to
> change transactions more:)
> I need a transaction, which will wait until other finish (either

other

> will commit, or rollback) with no error, and which will work with
> up-to-date values in tables, so I will not loose visitors in
> statistics:)
>
> Now I use
> ibase_trans

(IBASE_WRITE|IBASE_COMMITTED|IBASE_REC_NO_VERSION|IBASE_WAIT,$dbhi

> );
> Can you please help me?

If I guess write, you are doing something like this:

update countertable
set hits = hits + 1
where pageid = 5;

As you have found, this works fine until two transactions attempt to
do it at the same time. The method itself is flawed, and no amount of
parameter tweaking will fix it.

Firstly, let me explain what WAIT does.

Imagine two users hit page 5 at the same time. Two transactions are
started, transaction 1 is allowed to update the record in
countertable, but now there is a problem if we let transaction 2 hit
it.

By design, you would be unable to record two simultaneous hits, and
most approaches would use some sort of lock so that transaction 2
would wait until transaction 1 had finished. This approach works with
a small volume, but it does not scale well. Pages end up freezing
because a queue forms. The more the counter has to do, the worse it
will get.

Firebird does not work this way. It provides a consistent view of the
data to each transaction. Even though transaction 1 has changed the
value, transaction 2 can still see the old version. That would be
even worse if transaction 2 was allowed to make the change.

In the case where transaction 1 changes the value, we had better
prevent transaction 2 from changing it (because it can't even see the
most recent version). But imagine if after transaction 1 had changed
the value, it is rolled back? Well in this case it would be ok if
transaction 2 succeeded. So Firebird gives you the choice.

Do you want to be optimistic in Transaction 2 and assume that
Transaction 1 will fail? If so use WAIT. This will hang transaction 2
until transaction 1 commits or rolls back. If transaction 1 commits,
transaction 2 will get an exception.

If you are pessimistic in Transaction 2, you assume that Transaction
1 will succeed and you won't be able to continue. If so, use NOWAIT
and you get the exception immediately.

A better approach is to have a table that records hits as records

CREATE TABLE COUNTERTABLE
(
PAGEID INTEGER,
HITS INTEGER
);


Every time you get a hit to a page, do a simple INSERT with 1 as the
HITS, and commit immediately.

INSERT INTO COUNTERTABLE(PAGEID, HITS) VALUES (5, 1);

So your table looks like this after a while:

PAGEID HITS
====== =====
1 1
5 1
5 1
5 1
2 1
5 1

Now create a view

CREATE VIEW V_COUNTERTABLE (PAGEID, HITS) AS
SELECT PAGEID, SUM(HITS) AS HITS
FROM COUNTERTABLE;

And then select from this table whenever you want a count. But wait
you say, doesn't that get slow over time? Well if you left it, then
yes that would have a lot of work to do.

Create a Stored procedure to

for each page,
a) get hits from view for that page and store it.
b) delete all records from table for that page.
c) insert single record for that page with the hits you read in (a).

(I will leave it to you to write this procedure but it is pretty
trivial).

So what does this do?

PAGEID HITS
====== =====
1 1
2 1
5 4

Basically, it gives the view a lot less work to do in the future. Now
write a simple sql script that runs that stored procedure and also
batch file that calls iSQL and runs that script. Put the script into
the windows scheduler (or linux equivalent) and run it as frequently
as your data requires (eg hourly).

The benefit of this approach is that there is no waiting by anyone to
add one to the counter, and the more frequently you schedule the
stored procedure

a) The faster the view of the count will return; and
b) The faster the scheduled stored procedure will run.

There is no serialisation that would occur in your original model, no
need to worry about locks. The count is available even when the
stored procedure is running.

Adam