Subject | Transaction Isolation Levels |
---|---|
Author | Federico Tello Gentile |
Post date | 2004-09-01T12:00:05Z |
This comment is based on my short experience using FB and by working
in a project using Oracle.
I see people in the project where I am currently working spend time
and a lot of effort coding to achieve something I think should be
provided by the database engine.
This is a intranet web application with around 50 simultaneous users,
very interactive, lots of selects and inserts in the database, the
company has about 10 branches all using the same application instance.
There are a few (2 or 3) calculations that take about 5/10 minutes and
(like sending the bills to the stock managing system) and during that
calculation it is not desirable to have changes to that data. As the
current configuration in the oracle database (maybe the only
possible?) is that once you open a transaction if other transactions
commit, you see the changes even when you did not commit or rollback
your transaction people here are marking the rows as "in calculation"
in one transaction and then in a separate transaction doing the actual
calculation (that takes a while). This brings a lot of issues like
unmarking the rows on any error, taking care of any process not to use
the marked rows, etc.
In another place a different strategy was used. Oracle has a "select
for update" statement. This locks all the selected rows so nobody can
write to them. This is not automatic at all, you have to call that
statement explicitly.
My question or doubt is, isn't this much simpler in FB? My guess is
all you need is to set up Repeatable_Read isolation level and you will
never have data changed while you are inside one transaction. Am I
right?
If that is the case, when another transaction updates the data I'm
reading will those updates be stopped until my transaction finishes?
In the case the write is prevented, I think it would be the same as
the select for update with the non-trivial advantage of being
transparent to the application.
Sorry for the long post, maybe you could title it FB features you miss
when you are doing real work.
in a project using Oracle.
I see people in the project where I am currently working spend time
and a lot of effort coding to achieve something I think should be
provided by the database engine.
This is a intranet web application with around 50 simultaneous users,
very interactive, lots of selects and inserts in the database, the
company has about 10 branches all using the same application instance.
There are a few (2 or 3) calculations that take about 5/10 minutes and
(like sending the bills to the stock managing system) and during that
calculation it is not desirable to have changes to that data. As the
current configuration in the oracle database (maybe the only
possible?) is that once you open a transaction if other transactions
commit, you see the changes even when you did not commit or rollback
your transaction people here are marking the rows as "in calculation"
in one transaction and then in a separate transaction doing the actual
calculation (that takes a while). This brings a lot of issues like
unmarking the rows on any error, taking care of any process not to use
the marked rows, etc.
In another place a different strategy was used. Oracle has a "select
for update" statement. This locks all the selected rows so nobody can
write to them. This is not automatic at all, you have to call that
statement explicitly.
My question or doubt is, isn't this much simpler in FB? My guess is
all you need is to set up Repeatable_Read isolation level and you will
never have data changed while you are inside one transaction. Am I
right?
If that is the case, when another transaction updates the data I'm
reading will those updates be stopped until my transaction finishes?
In the case the write is prevented, I think it would be the same as
the select for update with the non-trivial advantage of being
transparent to the application.
Sorry for the long post, maybe you could title it FB features you miss
when you are doing real work.