At 05:08 PM 22/12/2004 +0000, you wrote:

>Hi,
>
>I created a transaction, did some queries, set savepoint, did
>another set of queries, set savepoint and so on
>after this quite long transaction I commited all.
>
>sidenote: this is not standard operation, it is done once per
>application setup (when the database is idle, no other connections,
>so this is not a heavy duty, many transactions problem.
>sidenote2: I am not doing any DDL within the transaction, pure
>Selects/inserts
>
>I experienced that at some point (i can't precisely define when) I
>start to receve strange exceptions.
>
>I found out that RELEASING SAVEPOINTS at the end of queries block
>(in the middle of the job regardless of commit at the end of the
>batch) helped with this issue.
>
>scenario:
>begin, fill table t1, set savepoint, fill related records in t2,
>release savepoint, set another, fill another set of related records
>in t2, release savepoint, commit transaction.
>
>Today I hit the problem again, this time I am releasing as I go, but
>I receive some strange results. (ie. the exact same procedure, give
>different results when called from different levels of the same
>transaction).
>
>I read through doc about savepoints and the only clue I find is the
>limit around 10^4 recoerds affected.
>I don't think that I hit that amount within single transaction.
>How many savepoints can be set without commit. (how many levels deep
>can the transaction be)

There is a limit, though I don't know what it is. It's not just your user
savepoints that get accumulated to the total, but also the internal
savepoints used by the engine.

>Can I safely read from records I inserted in current transaction but
>some levels before possible after few partial commits (savepoints
>releases)?

I think you are misunderstanding the use of savepoints. Savepoints don't
commit anything. They simply flag points in the execution of statements
within your transaction to which you can undo changes, without undoing
changes that were posted prior to the savepoint. IOW, user savepoints have
the effect of nested transactions - atomic blocks of execution within the
atomic block that is the enclosing transaction.

So, typically, your client code will be characterised by branching logic in
response to exceptions that occur during the course of the transaction:

if (ExceptionOccurs) then ROLLBACK TO savp1;

If that's not how you're using savepoints then don't use them.

You can economise on savepoints by reusing the same savepoint identifier
once your program logic doesn't need the savepoint any more. By calling
SAVEPOINT savp1, you release the existing flag that savp1 is holding and
reuse it to flag the current point of execution.

>I know I should not update the same record multiple times
>within the same transaction. I am not doing this, but what could be
>the consequences, is it defined as no (if so why there is no
>exception from Firebird) or it only bails out under some
>circumstances (what are those if so).

The engine won't stop you from hitting the same row multiple times, but
each "hit" on the same row increases the number of internal
savepoints. When the limit is reached, you will get an exception ("Too
many savepoints").

Frankly, if your program logic (or your PSQL logic in SPs or triggers) is
such that your transaction *needs* to hit the same row more than once, then
it's very likely your program logic needs reviewing. Sometimes,
programmers forget that *every* change to a row replaces the entire
row...so troubleshooters often encounter PSQL or host code that calls
UPDATE multiple times on a single row, i.e. once for each column that the
application or procedure affects. It's a much healthier approach to design
row updates so that all of the changes occur in a single statement.

./hb