DBConcurrencyException

This issue is related to the closed ticket [343d392b51]
In fact the problem still remains. Please read the old ticket first.

As a workaround I now do not use adapter.Update() any longer (as it raises the exception).
Instead of

String  table = “table”;
try {
sql = "select * from table where ...";
da = new SQLiteDataAdapter(sql, db_con);
da.Fill(ds, table);
dt = ds.Tables[table];
…
manipulate_data();
int i = da.Update(dt); here the DBConcurrencyException raises !!!

I now use a stupid sql.command in a loop.
…
String sql = String.Format("update table set … “

SQLiteCommand cmd = new SQLiteCommand(sql, dbconnection);
return cmd.ExecuteNonQuery();
…

This works, but it does not perform well and it is very ugly code.
________________________________

The DBConcurrencyException raises not on every adapter.Update() statement. Sometimes it works, sometimes not. Sometimes a few records are updated, sometimes just only one, sometimes none. Sometimes it raises on this table, sometimes on the other table.
All my tables have as a first column a DateTime column (“CHANGE_DATE”).
It does not matter whether I do
row[“CHANGE_DATE”] = DateTime.Now;
Or
row["CHANGE_DATE"] = DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day + " " + DateTime.Now.TimeOfDay;

regards,
Simon

Are you using DateTimeFormat=JulianDay or UnixEpoch in the connection string?  This is necessary to take advantage of the fix for the previous ticket you mentioned.

If I use > DateTimeFormat=JulianDay < in the connectionstring the statement

  xydatetime = reader.GetDateTime(0);

raises an exception:

TypeInitialisationException:  wrong format
_______

String sql = "select CHANGE_DATE, ...     from TABLE .. ";

SQLiteCommand cmd = new SQLiteCommand(sql, dbconnection);

SQLiteDataReader reader = cmd.ExecuteReader();

 if (reader.HasRows)
     while (reader.Read())
     {
       ...
       xydatetime = reader.GetDateTime(0);
       ...

       }

  reader.Close();


When you use the JulianDay format, the type conversion code assumes that database fields with the type DATETIME (or DATE, etc) will actually be a floating point number.  Likewise, when you use the UnixEpoch format, it assumes that database fields with the type DATETIME will actually be an integer number of seconds since the Unix epoch.


connection string contains > DateTimeFormat=JulianDay <

the following code raises System.FormatException:

String Sql = "Select CHANGE_DATE, … from table ..”;

da = new SQLiteDataAdapter(sql, dbconnection);

da.Fill(dset, table);

The column CHANGE_DATE is of type DATETIME.

Why did you close this ticket ?

Because the behavior you appear to describe is not a bug.  When using the UnixEpoch or JulianDay formats in the connection string, the underlying data in the database itself must be of the type expected by the type conversion code in System.Data.SQLite.  For UnixEpoch, it must be an integer.  For JulianDay, it must be a double-precision floating-point number.

Does that mean that I can't use columns of type DatTime ?
Just to use the DateTimeFormat=JulianDay does not convert the underlying data in the database. Right ? Do I have to convert the data in the database first before working on it ?

Effectively, yes.  The DateTimeFormat property of the connection string controls how System.Data.SQLite attempts to *interpret* the actual data.  If you want to use JulianDay or UnixEpoch (or Ticks), the actual data in the database must be changed first.  Alternatively, you can use ISO8601 if your DateTime strings match one of the supported format strings.

this issue is drivin me nuts

I tried:

first version

connectionstring:   DateTimeFormat= JulianDay

table product:

change_date  datetime,
price number,
name varchar2,
id integer

firstly, I did:

update product set change_date = 2455928.0    is 2012-01-01 12:00:00

then:

sql = “select change_date, id, name, price from product”;

adapter =  new SQLiteDataAdapter(sql, db_con);

adapter.Fill(ds, datatab);

….

row[“price”]  += 1;

row[“change_date”]  =  DateTime.Now;

….

adapter.update(datatab);     <- here the DBConcurrencyException raises

_______________________________________________

second version:

same table;

connectionString:     DateTimeFormat= JulianDay

update product set change_date = 2455928.0    is 2012-01-01 12:00:00

then

!! difference !!

sql = “select julianday(change_date) as change_date, id, name, price from product”;

adapter =  new SQLiteDataAdapter(sql, db_con);

adapter.Fill(ds, datatab);

….

row[“price”]  += 1.0;

in fact now the column is of type System.Double

row[“change_date”]  =  (double)row[“change_date”]  + 1.0;

….

adapter.update(datatab);

no exception raises, but what happens is:

the price column is updated, the DateTime-Column is not  !

What's wrong ?


<verbatim>
Instead of:

row["change_date"] = DateTime.Now;

Try:

row["change_date"] = SQLiteConvert.ToJulianDay(DateTime.Now);
</verbatim>


row["change_date"] = SQLiteConvert.ToJulianDay(DateTime.Now);

=> compiler says:

An object reference is required for the non-static field, method, or property 'System.Data.SQLite.SQLiteConvert.ToJulianDay(System.DateTime)'

Ok, that is an issue because those type conversion methods should be static as they do not need any instance data.

Changed to static by check-in [114024ffe7], with tests.


connectionstring: DateTimeFormat= JulianDay

update product set change_date = 2455928.0    which is 2012-01-01 12:00:00

change_date is of type DateTime !

----------------

sql = “select change_date, price, ....”;

adapter = new SQLiteDataAdapter(sql, db_con);

adapter.Fill(ds, datatab);

….

row[[price]] += 1;

row[[change_date]] = SQLiteConvert.ToJulianDay(DateTime.Now);      <-

TypeConverionException raises,

which is clear, because:

the original SQLite table-column is of type DateTime,
then the ADO-table is also of type System.DateTime, to which we want to assign a double.

System.ArgumentException: invalid type conversion "Double" to "DateTime".<2455941,93204139> cannot be saved to CHANGE_DATE column.
Expected type: DateTime. ---> System.InvalidCastException: invalid conversion "Double" to "DateTime".


I forgot that the necessary conversions happen internally.  Here is an <a Basically, this means the statement should actually be something like:
<verbatim>row["change_date"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.FFFFFFFK");</verbatim>

I still have the DBConcurrencyException issue.

I now use DateTimeFormat=JulianDay as you recommended.
I converted the data to floats as described:
update product set change_date = 2455928.0 which is 2012-01-01 12:00:00

I now use:
row["change_date"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.FFFFFFFK");

but the DBConcurrencyException still raises (not everytime, but most often)

Did you see my last comment ? The issue still exists.
I tested with - no changes.
I think it's a deeper problem, probably linked to [d76b409d07]
What can I do ?

I'm still unable to reproduce the problem at all here.  One thing to try (using the latest checked-in code, not is to set the Flags property of the SQLiteConnection object to LogPrepare and then watch the trace output to see what SQL statement is returning zero records.  Then, add that SQL statement to this ticket so I can figure out where things are going wrong.


ok, will do.
for the first the exception in detail:

e.Source:
System.Data

e.Data:
System.Collections.ListDictionaryInternal

e.StackTrace:
   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
   at Test.mytest.mytest.UpdateTest(DataTable dt) in Studio 236.

e.TargetSite:
Int32 UpdatedRowStatusErrors(System.Data.Common.RowUpdatedEventArgs, BatchCommandInfo[], Int32)

e.Message:
Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

The exception information itself is not particularly illuminating.  The SQL statement causing the issue will be far more informative.


5 rows where updated (of ~200) then the exception ocurred.

trace output:

...

Prepare: 123241624

SQLite message (0): Preparing {UPDATE [main].[PRODUCT] SET [CHANGE_DATE] = @param1, [ID] = @param2, [PRICE] = @param3 WHERE (([CHANGE_DATE] = @param4) AND ([ID] = @param5) AND ((@param6 = 1 AND [PRICE] IS NULL) OR ([PRICE] = @param7)))}...

Prepare: 123239424

'Test.vshost.exe' (Managed): Loaded

A first chance exception of type 'System.Data.DBConcurrencyException' occurred in System.Data.dll

...


Judging from the query you posted, it looks like this could again be a DateTime format issue since there is a DateTime column in the WHERE clause.  Would it be possible to figure out what parameter values are being used at that point?

again 5 rows where updated, then the exception ocurred

code:

            cstr = new SQLiteConnectionStringBuilder();

            cstr.DataSource = myDB;

            cstr.Version = 3;

            cstr.DateTimeKind = DateTimeKind.Local;

            cstr.DateTimeFormat = SQLiteDateFormats.JulianDay;

            cstr.Flags = SQLiteConnectionFlags.LogPrepare;

...

            for (int i = 0; i < rcount; i++)

            {

                System.Threading.Thread.Sleep(5000);

                dt.Rows[i]["PRICE"] = (double)dt.Rows[i]["PRICE"] + 5.123;

                string s = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.FFFFFFFK");

                dt.Rows[i]["CHANGE_DATE"] = s;

            }

---

adapter.Update();

string s outputs:

2012-02-09 23:51:24.746+01:00

2012-02-09 23:51:29.756+01:00

2012-02-09 23:51:34.762+01:00

2012-02-09 23:51:39.772+01:00

2012-02-09 23:51:44.777+01:00

2012-02-09 23:51:49.787+01:00

2012-02-09 23:51:54.797+01:00

2012-02-09 23:51:59.807+01:00

2012-02-09 23:52:04.817+01:00

2012-02-09 23:52:09.823+01:00

2012-02-09 23:52:14.829+01:00

2012-02-09 23:52:19.835+01:00

and so on

I've been trying to reproduce this issue here based on the modified example you provided; however, no matter what variation I try, I cannot get the concurrency exception.  Here is my current test case (the complete C# code is embedded within the test script itself):

<verbatim>
runTest {test tkt-4bbf851fa5-1.1 {SQLiteDataAdapter update fail} -setup {
  setupDb [set fileName tkt-4bbf851fa5-1.1.db]
} -body {
  set id [object invoke Interpreter.GetActive NextId]
  set dataSource [file join [getDatabaseDirectory] $fileName]

  set sql(inserts) ""
  set sql(1) [subst { \
    CREATE TABLE t1(y DATETIME PRIMARY KEY, z FLOAT); \
    [for {set i 1} {$i < 10} {incr i} {
      append sql(inserts) [appendArgs \
          "INSERT INTO t1 (y, z) VALUES(JULIANDAY('" \
          [clock format $i -format [getDateTimeFormat]] "'), " $i "); "]
    }; return [expr {[info exists sql(inserts)] ? $sql(inserts) : ""}]] \
  }]

  set sql(2) [subst { \
    SELECT y, z FROM t1 ORDER BY y; \
  }]

  unset -nocomplain results errors

  set code [compileCSharpWith [subst {
    using System;
    using System.Data;
    using System.Data.SQLite;

    namespace _Dynamic${id}
    {
      public class Test${id}
      {
        public static void Main()
        {
          SQLiteConnectionStringBuilder connStr =
              new SQLiteConnectionStringBuilder();

          connStr.DataSource = "${dataSource}";
          connStr.Version = 3;
          connStr.DateTimeKind = DateTimeKind.Local;
          connStr.DateTimeFormat = SQLiteDateFormats.JulianDay;
          connStr.Flags = SQLiteConnectionFlags.LogPrepare;

          using (SQLiteConnection connection = new SQLiteConnection(
              connStr.ToString()))
          {
            connection.Open();

            using (SQLiteCommand command = connection.CreateCommand())
            {
              command.CommandText = "${sql(1)}";
              command.ExecuteNonQuery();
            }

            using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(
                "${sql(2)}", connection))
            {
              using (DataSet dataSet = new DataSet())
              {
                dataAdapter.Fill(dataSet, "t1");

                DataTable dataTable =

                = true;
                dataTable.PrimaryKey = new {

                };

                [expr {[isMono] ? "#pragma warning disable 219" : ""}]
                SQLiteCommandBuilder commandBuilder =
                    new SQLiteCommandBuilder(dataAdapter);
                [expr {[isMono] ? "#pragma warning restore 219" : ""}]

                for (int index = 0; index < dataTable.Rows.Count - 1; index++)
                {
                  System.Threading.Thread.Sleep(1000);

                  =
                      DateTime.Now.ToString("[getDateTimeFormat]");

                  =
                      + 1.123;
                }

                dataAdapter.Update(dataTable); DBConcurrencyException (?)
              }
            }
          }
        }
      }
    }
  }] true true true results errors System.Data.SQLite.dll]

  list $code $results \
      [expr {[info exists errors] ? $errors : ""}] \
      [expr {$code eq "Ok" ? [catch {
        object invoke _Dynamic${id}.Test${id} Main
      } result] : [set result ""]}] $result
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -match \
regexp -result {^Ok System#CodeDom#Compiler#CompilerResults#\d+ 0
</verbatim>


By any chance, is there another thread or process modifying the database at the same time as the data adapter is trying to?

I've sent an email to sqlite-dev with example code an a db-file which generates the exception. Did you see it ?

I haven't seen it yet.  Also, since the mailing list strips attachments, it might be best to upload it somewhere public (e.g. Dropbox) and then provide a link to it in the email.

here is the link:

db-file:

code:

I found another strange behaviour:
the test code likes to override the price with the value 5.123.
the first ten rows are updated but with the value 5, not 5.123.
Then the exception occurs.
(it's the same code i've sent by email)


Thanks.  I think your example will be helpful in tracking this issue down.  As for the PRICE issue, the "NUMBER" column type is not recognized by SQLite nor System.Data.SQLite.  I would use DOUBLE or REAL instead.


It appears the mixture of data in the database combined with the NUMBER column type is the root cause of the issue.  For example, try this query with your database:

<verbatim>
    SELECT typeof(PRICE) FROM product;
</verbatim>

Notice that some of the results say "integer" and others "real".  For SQLite, this is not a big deal, since it will simply default the column affinity to TEXT; however, System.Data.SQLite uses the column type information to provide a mapping to the actual .NET type and database column type.  In the case of "NUMBER", it returns the default, DbType.Object.

At that point, I think .NET attempts to figure out the type of the column based on the data currently in it for the row (this is just an educated guess) and then attempts to convert the new value to that type.  If the new value ends up being truncated from REAL to INTEGER, for example, it could result in 5.123 becoming 5, as you stated.  This may cause the exception, for example, if the value was actually 5 to start with.


For the future, I've added a Trace warning (in the Debug build only) to the type mapping code, see check-in [10fae679b2].


thanks.

and just for your information:

you also closed ticket [d76b409d07] which was not my ticket.
(and this guy uses only varchars and one integer prim key,
seems that my problem is not his problem)

Simon

Given the rather extensive amount of analysis and testing I have conducted on these concurrency exception issues and the fact that this ticket and another ticket like it were both caused by data type conversion issues outside of the direct control of the library, I am fairly confident that the final such ticket, [d76b409d07], does not actually represent a real issue with SQLite or System.Data.SQLite.  That being said, if the original reporter discovers new information or evidence to point to an actual issue, he can always re-open the ticket.


if tested - no changes.

I started with a complete new db-file, created a table product with one Datetime column, one integer prim key column and then only text and real columns.
Again I inserted my 180 products.

after round about ten runs of my program with each updates round about 30 rows correctly with no problems the exception ocurrs again, having updated only 15 rows.

running the given code with the given dbfile will update again only 15 rows before the exception occurs.


correct links:


After researching this issue, it appears to be caused by rounding errors in the double precision values being used for the Julian Day numbers.  For example, the UPDATE query generated by the .NET Framework data adapter subsystem for the first row in the table looks something like this:

<verbatim>
  UPDATE [main].[t1] SET [y] = @param1, [z] = @param2
  WHERE (([x] = @param3) AND ([y] = @param4) AND ([z] = @param5))
</verbatim>

And the values for the parameters are as follows:

<verbatim>
  @param1 = 2455976.6870139237 (double)
  @param2 = 9.876 (double)
  @param3 = 1 (long)
  @param4 = 2455975.9869903936 (double)
  @param5 = 5.123 (double)
<verbatim>

The problem is that the actual first row in the table contains this data:

<verbatim>
  INSERT INTO t1 (x, y, z) VALUES(1, 2455975.98699039, 5.123);
<verbatim>

This causes the WHERE clause in the above UPDATE query to match zero rows because 2455975.98699039 (in database) is not equal to 2455975.9869903936 (query parameter).