Hi All,
In my case there is no large data to be analysed, only a timer that checks whether it is midnight or not. If it is midnight then update a single row in database this is done using stored procedure. Confusing part is below error occurs only between midnight that is from 12 to 2 AM. During this time stored procedure executed will return deadlock error or timeout exception. 1. “ERR::-2147467259: Deadlock concurrent transaction number is 5744401” 2. ERR::Error reading data from the connection.; FirebirdSql.Data.FirebirdClient 3. ERR::Timeout exceeded.; FirebirdSql.Data.FirebirdClient: ERR::-2146233079: Connection must be valid and open at FirebirdSql.Data.FirebirdClient.FbCommand.CheckCommand() at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteScalar() I don’t think it has to do anything with FB as it this same stored procedures work fine during day time without any issues. Thank you. From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 03 December 2017 06:08 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Deadlock error from firebird during midnight On Sun, Dec 3, 2017 at 5:04 PM, DougC d...@moosemail.net <mailto:d...@moosemail.net> [firebird-support] <firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com> > wrote: Please, let us know what the cause ended up being and how you resolved the problem! Alright, I will explain the issue in detail. 1. I have a .NET application of mine deployed since about last 7 yrs with about 90 tables in the firebird database. Very routinely, as per customer’s requirement, I need to carry out changes to database structure [adding / renaming / removing – field(s) OR table(s)]. After doing such updates to my database structure I copy data to the new design database, through a .NET application. 2. Recently after about 7 yrs, I started to get exception “Error reading data from the connection” during transfer of data for a table which now had grown to about 13,26,000 records. For this table I was using FbDataAdapter to copy data in batches of 50,000 records. 3. I observed that every time the exception was raised after copying 12,50,000. So from the source, I deleted the already copied 12,50,000 records and resumed to copy the remaining 76,000. This too failed. 4. Later all my attempts as under failed: (a) using FbDataReader in place of FbDataAdapter. (b) changing connecting string elements like Connection Timeout, Connection lifetime, Pooling. (c) Copying after backup-restore of both source and destination database. (d) Changing machines. 5. So I wrote a stored procedure with inputs from Dimitry which copied records using execute Statement On External database. This stored procedure I executed through FbCommand's ExecuteNonQuery. This worked fine. 6. Now on Dimitry’s advice I changed the FbTransaction from: FbTransaction tx = con.BeginTransaction(); To: FbTransaction tx = con.BeginTransaction( new FbTransactionOptions() {TransactionBehavior = FbTransactionBehavior.Concurrency | FbTransactionBehavior.NoWait}); And found that FbDataAdapter also started to work correct. 7. The option at Para 5 is much faster. In case you need more details / code I will please share. Regards, Rajiv ---- On Sat, 02 Dec 2017 23:08:49 -0500 LtColRDSChauhan <mailto:rdsc1...@gmail.com> rdsc1...@gmail.com [firebird-support] < <mailto:firebird-support@yahoogroups.com> firebird-support@yahoogroups.com> wrote ---- Thanks a lot, Dimitry ! This issue of mine, for which I had posted for help on the firebird-net-provider group, is now resolved.
