SQLDB Transaction isolation level settings

[patyi]

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]

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]

Yes 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]

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]

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]

Write per line:
isc_tpb_read_commited
isc_tpb_wait

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

[Zoran]

Thank you!

[BigChimp]

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_transactions
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]

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

[BigChimp]

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 snapshot
• Snapshot: I don't need read committed, as there won't be any other commits. Perhaps snapshot gives better performance
• Consistency: might be the best option; I could just lock all tables involved (or all tables in the db for that matter)