What is the best transaction isolation parameter for FireBird lazarus / 16-Databases ---------------------------------------------------------------------- motaz · 2014-01-20 09:17 I'm developing client/server small ERP system using Lazarus and FireBird, what is the best transaction parameters should I use. I'm using SQLdb components. Sometimes TurboBird crashes when I open this ERP application and trying to access some tables, but not always, it rarely happens. In TurboBird, I set transaction parameters to isc_tpb_read_commited my goal to eliminate the crash and system hang ---------------------------------------------------------------------- JD · 2014-01-20 09:40 Hi there Motaz, Let me share my application's Firebird connection module with you Code: [Select]constructor TConnectionModule.Create(TNSNAME, LOGIN_ID, LOGIN_PW, AHostName: string; APort: integer   ); begin   // Create the DB Connection to a Firebird database   SQLConnection := TZConnection.Create(nil);   // This is the default IP if the configuration file does not specify an alternative path   // The HostName property is important in case the application server   // and the database itself are not on the same machine   SQLConnection.HostName := AHostName;   // If the application server and the database are on the same machine,   // set the database port number to zero   //   // Localhost does NOT need a port number!   // The bug is caused by using non-zero port numbers on localhost   //   SQLConnection.Port := APort;    // Use default is HTTP Port #80 if database is not on same machine as server   SQLConnection.Database := TNSNAME;   SQLConnection.Protocol := 'firebird-2.5';   SQLConnection.User     := LOGIN_ID;   SQLConnection.Password := LOGIN_PW;   SQLConnection.AutoCommit := False;   SQLConnection.TransactIsolationLevel := tiReadCommitted;   // Set the ConnectionModule properties to UTF-8   with SQLConnection.Properties do   begin     Add('character_set_client=utf8');     Add('character_set_connection=utf8');     Add('character_set_database=utf8');     Add('character_set_results=utf8');     Add('character_set_server=utf8');     Add('character_set_system=utf8');     Add('collation_connection=utf8_general_ci');     Add('collation_database=utf8_general_ci');     Add('collation_server=utf8_general_ci');     Add('Codepage=utf8');     Add('isc_tpb_concurrency');              // Needed for multiuser environments     Add('isc_tpb_nowait');                   // Needed for multiuser environments   end; end; As you can see, I use isc_tpb_concurrency & isc_tpb_nowait as well as a tiReadCommitted transaction isolation level. I'm using Zeos components but it works just as well for SQLdb. JD ---------------------------------------------------------------------- motaz · 2014-01-20 10:40 Thank you JD, I'll use it for a while and let you know. Code: [Select]  Params.Add('isc_tpb_read_commited');   Params.Add('isc_tpb_concurrency');   Params.Add('isc_tpb_nowait'); ---------------------------------------------------------------------- Zaher · 2014-01-20 11:02 >Sometimes TurboBird crashes when I open this ERP application and trying to access some tables Crashes or Exception with error message? ---------------------------------------------------------------------- motaz · 2014-01-20 13:16 it closes only without any error message ---------------------------------------------------------------------- patyi · 2014-01-20 17:20 Hi JD ! Witch version of Zeos you are use  ? ---------------------------------------------------------------------- Zaher · 2014-01-20 17:39 @Motaz You need to emulate the dead lock to see if it is the real problem. ---------------------------------------------------------------------- JD · 2014-01-20 21:43 Quote from: patyi on January 20, 2014, 06:20:15 pmHi JD ! Witch version of Zeos you are use  ? The most recent stable version 7.1.2 ---------------------------------------------------------------------- motaz · 2014-01-21 05:30 Quote@Motaz You need to emulate the dead lock to see if it is the real problem. Today I need to use both applications (TurboBird and ERP) to access the same database, and I'll keep monitor their behavior. ---------------------------------------------------------------------- BigChimp · 2014-01-21 07:35 @motaz: had a very quick look at turbobird source code. Impressive stuff with threading etc (which is definitely not one of my areas of expertise). Some remarks that you may hopefully find useful but do not pertain directly to transaction isolation level: 1. You seem to be using synapse? (httpsend) but don't include the code in your repo? No problem but perhaps mentioning this in a readme.txt as a requirement would be a good idea. 2. You seem to be using {$IFDEF MAC} to detect Apple OSX systems - I'm a bit surprised this works http://www.freepascal.org/docs- html/prog/progap7.html suggests you should use {$IFDEF DARWIN} instead... 3. In the code where you perform the actual execution of the queries you are catching general Exceptions but it might be better to first try and catch the specific Firebird error first e.g. Code: [Select]on E: EIBDatabaseError do    begin                    //or showmessage or whatever logging you use       Writeln(stderr, 'Debug: ', DateTimeToStr(Now),         ': Database error message: ',     E.Message, '; Database error code (GDS code): ', E.GDSErrorCode );   end; The code looks very interesting - I'll put it on my to do list to have a look and see if I can make some small improvement suggestions/patches... Thanks, BigChimp ---------------------------------------------------------------------- motaz · 2014-01-21 11:54 @BigChimp thank you very much for your remarks I have done the first two (1 and 2), but number 3 needs more time, this is a bad habit of me to use the general exception. I was done the same in Java, but now NetBeans 7.4 suggests to developer to modify it to a specific exception type, and it modifies it automatically according to the expected exceptions of protected code. After a day of using JD parameters, things work fine for 3 hours (no exceptions, no hang) but firebird engine hang up, and this is first time I encounter FireBird hang up in Linux. I noticed that connection to defected FireBird database makes the clients wait for long time, until I have forced closed them. Is there anyway to set a connection timeout? ---------------------------------------------------------------------- Zoran · 2014-01-21 13:42 Quote from: motaz on January 21, 2014, 12:54:55 pmIs there anyway to set a connection timeout? There was a topic about it recently: http://forum.lazarus.freepascal.org/index.php?topic=23115.0 I'm not sure that it is much helpful, though... ---------------------------------------------------------------------- miab3 · 2014-01-21 13:45 @motaz Use the current stable version ZEOS 7.1.3a: http://sourceforge.net/projects/zeoslib/files/Zeos%20Database%20Object s/zeosdbo-7.1.3a-stable/ZEOSDBO-7.1.3a-stable.zip There's a few amendments to the Firebird. And better yet, use the development version 7.2- Zeos7.2-alpha branches_ testing_R3072: http://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.2/ http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=3795&p=25561 Michal ---------------------------------------------------------------------- BigChimp · 2014-01-21 14:23 Quote from: Zoran on January 21, 2014, 02:42:49 pmQuote from: motaz on January 21, 2014, 12:54:55 pmIs there anyway to set a connection timeout? There was a topic about it recently: http://forum.lazarus.freepascal.org/index.php?topic=23115.0 I'm not sure that it is much helpful, though... Yep... I don't think you can set a connection timeout for new connection attempts (which that topic talked about). Haven't really found anything about timeouts for waiting for query results etc. Edit: though perhaps the tpb_lock_timeout may have an influence http://wiki.lazarus.freepascal.org/Firebird_in_action#Various_options You can kick connections out of a Firebird 2.5 server but that takes place within the server so not much use if it is actually hanging... ---------------------------------------------------------------------- JD · 2014-01-22 15:21 Quote from: motaz on January 21, 2014, 12:54:55 pmAfter a day of using JD parameters, things work fine for 3 hours (no exceptions, no hang) but firebird engine hang up, and this is first time I encounter FireBird hang up in Linux. I noticed that connection to defected FireBird database makes the clients wait for long time, until I have forced closed them. Is there anyway to set a connection timeout? I did not even try to set a connection timeout because generally connecting to a database takes time. So I use a fixed size connection pool managed by application server that runs 24/7. When the application server starts, it creates the connection pool & then waits idly for connections from clients. The connection speed improvements are incredible. I only close the application server when I need to add new features or fix bugs. The last time I did so was 7 days ago. JD ---------------------------------------------------------------------- motaz · 2014-01-22 18:39 JD what is your application server, is it a middle ware that you have develop? what is it's idea? ---------------------------------------------------------------------- JD · 2014-01-23 05:44 Quote from: motaz on January 22, 2014, 07:39:06 pmJD what is your application server, is it a middle ware that you have develop? what is it's idea? The application server is middleware that I developed using Lazarus (of course). I got the idea from watching how an internet cafe functions and from how telecom companies manage subscriber messages. Basically in an internet cafe the computers are connected to a server which manages available computers, time spent on computers & bills appropriately. SMS messages are also managed by servers which route messages between subscribers. So instead of managing time & bills or SMS, my application server manages client profiles, connections, database queries, notifications & messages. I had to write a customized data protocol for this but it was not difficult. The clients are just "shells"/GUIs, no database modules or components on them. All that is on the application server. On their own the clients are useless. They need the application server which in turn needs the database (Firebird now but it can be any other database) to function properly. JD ---------------------------------------------------------------------- motaz · 2014-01-23 08:07 @JD Does that mean it is a socket proxy lay between client and database server? ---------------------------------------------------------------------- JD · 2014-01-23 08:28 Quote from: motaz on January 23, 2014, 09:07:31 am@JD Does that mean it is a socket proxy lay between client and database server? Yes I'm using an Indy10 transport layer to pass data as streams between the clients & the server. ---------------------------------------------------------------------- motaz · 2014-01-24 06:53 JD how do you use data controls/datasets in the client, did you populate the data manually from the stream socket? ---------------------------------------------------------------------- JD · 2014-01-24 08:50 Quote from: motaz on January 24, 2014, 07:53:04 amJD how do you use data controls/datasets in the client, did you populate the data manually from the stream socket? I use collections. Specifically the TCollections class. See http://www.atug.com/andypatterns/collections.htm. This is just an example. It is an old writeup but there are many others on the Web. This, apart from the data protocol is what the server & the client have in common. In addition collections offer streaming support; so they can be transferred using sockets. I did not look at Generic collections because they were not very reliable in FreePascal when I decided to change my architecture from data controls/datasets in the client to collections. I will do so in the not too distant future. See the attached file for a simple example of how I did this. The only thing missing is the sockets part. You use Synapse & I use Indy but they are both very capable of transferring streams. One can even use LNet if desired. JD