SQLDB Transaction isolation level settings
lazarus / 16-Databases

----------------------------------------------------------------------
patyi · 2011-09-22 21:20

Hi all !

One interesting question, that I don't find on the forum, nor in
documentation :
Howe can I set the transaction isolation level for Firebird 2.5 in
SQLDB Transaction components Params property ? 
For example to ReadCommited !?
What is the default isolation level for SQLDB ?
(if it is ReadCommited then I'm happy ! if not then ...  )

Can I use more then one transaction component / connection ?

(Lazarus latest SVN, FPC 2.7.1 latest SVN, Firebird 2.5, Ubuntu 11.04)

Regards : Patyi

----------------------------------------------------------------------
Lacak2 · 2011-09-23 04:33

Yes you can use Params property and there you can set:
"isc_tpb_read_committed"
with conjuction with otheres like:
"'isc_tpb_wait" etc.

----------------------------------------------------------------------
Zoran · 2011-09-28 22:05

Quote from: Lacak2 on September 23, 2011, 06:33:29 amYes you can use
Params property and there you can set:
"isc_tpb_read_committed"
with conjuction with otheres like:
"'isc_tpb_wait" etc.

Could you please write an example of what shoud be exactly put in
Params to set the transaction isolation level to read committed? Thank
you.

----------------------------------------------------------------------
patyi · 2011-09-29 05:50

Hi !

Transaction parameters for READ COMMITED is in theory should bi :

isc_tpb_read                          - read permission
isc_tpb_write                          - write permission
isc_tpb_read_committed     - transaction isolation level
isc_tpb_wait                           - if another transaction is use
the record then wait
isc_tpb_rec_version             - keep old record versions

This transaction isolation level is best for short transactions !

I need confirmation of this from Firebird expert ...

Thanks, Patyi.

----------------------------------------------------------------------
Zoran · 2011-09-29 06:51

If I am not mistaken, Params property is a list of lines which should
have param=value form. I don't know what to put there —
something=isc_tpb_read_commited?

----------------------------------------------------------------------
Lacak2 · 2011-09-30 05:21

Write per line:
isc_tpb_read_commited
isc_tpb_wait

(so no "key"="value", only "values" per line)

----------------------------------------------------------------------
Zoran · 2011-09-30 18:53

Thank you!

----------------------------------------------------------------------
BigChimp · 2011-10-10 16:15

Hi all,

Thanks for the info above.
I've started to note it down in the wiki:
http://wiki.lazarus.freepascal.org/Firebird_in_action#Advanced_transac
tions
but couldn't figure out the other isolation levels.

Help in updating the info there (or additional links) very welcome.

Also, as long as I have your attention: I'm trying to speed up a query
on a couple of tables in a Firebird 2.5 embedded database. My
connection/transaction/query is the only one and it can have read-only
access. In other words, I don't care about updates etc.
What is the best combination of settings to use?
Thanks.

----------------------------------------------------------------------
Lacak2 · 2011-10-11 05:28

You can look at http://conferences.embarcadero.com/article/32280

----------------------------------------------------------------------
BigChimp · 2011-10-11 08:00

Thanks, that helps, updated the wiki.
Now I found the various transaction isolation levels, and a mapping of
all parameters.

As for my question regarding my db, am I correct in this assessment:
Read committed: will have overhead looking for other commits - but I'm
the only transaction on that embedded db, so will probably be slower
than snapshotSnapshot: I don't need read committed, as there won't be
any other commits. Perhaps snapshot gives better
performanceConsistency: might be the best option; I could just lock
all tables involved (or all tables in the db for that matter)