Cant insert into Identity Column when Multithreading

Question

I have a data migration tool which until recently seemed to work really well.

I run through a collection of data rows using Parallel.Foreach, calculate some variables to be inserted into a new record of a table and then run an SQL statement to insert the data.

Parallel.ForEach<DataRow>(dataTable.Rows, row =>
{
     string reference = row["ref"].ToString();
     int version = (int)row["version"];

     string insertStatement = "INSERT INTO Item (Reference, Version) VALUES (@reference, @version)";

     _database.ExecuteCommand(insertStatement, new SqlServerCeParameter[]
     {
         new SqlServerCeParameter("@reference", reference, SqlDbType.NVarChar),
         new SqlServerCeParameter("@version", version, SqlDbType.Int),
     });
});

public void ExecuteCommand(string sql, SqlServerCeParameter[] parameters)
{
    //create the command that will execute the Sql
    using (var command = new SqlCeCommand(sql, _connection))
    {
        //add any parameters
        if (parameters != null) command.Parameters.AddRange(parameters.Select(p => p.ParameterBehind).ToArray());

        try
        {
            //open the connection 
            if (_connection.State == ConnectionState.Closed) _connection.Open();

            //execute the command
            command.ExecuteNonQuery();
        }
        catch (SqlCeException ex)
        {
            //only catch if the native error is the duplicate value exception otherwise throw as normal
            if (ex.NativeError != 25016) throw;
        }
        finally
        {
            //explicitly close command
            command.Dispose();
        }
    }
}

However I get an aggregate exception, the inner exception of which is as follows:

{"The column cannot contain null values. [ Column name = ID,Table name = Item ]"}

The structure of the table is as follows:

CREATE TABLE Item
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Reference NVARCHAR(50) NOT NULL,
    Version INT NOT NULL
);

Now I don't understand this error as the ID is an identity column.

One thought that I had was that it cant calculate two ids at the same time because of the multithreading but this seems like a really flimsy reason as SqlServerCe is meant to be ok for multi user environments.

Welcome to the world of multi-threading! The problem could be that two threads both decide to insert record with ID:2 because they are running in parallel... One will lose... — Belogix, Sep 16 '14 at 15:30
@Belogix does SQL Server Compact not handle this automatically??? — DavidG, Sep 16 '14 at 15:32
Im only going by the exception message which gives the ID column as the offending article — Steven Wood, Sep 16 '14 at 15:37
Perhaps SET IDENTITY_INSERT was set ON before. Did you check it? — Alireza, Sep 16 '14 at 15:44

score 1 · Accepted Answer · answered Sep 16 '14 at 15:41

1

IMPORTANT: SQL CE objects are not thread-safe. You are using _connection in each call which I am guessing is a single instance of SqlCeConnection?

It is recommended that each thread should use its own separate connection rather than sharing it across multiple threads. So, try creating a new SqlCeConnection in your ExecuteCommand method and connecting each time.

This may not achieve the speed increase you were hoping / expecting but then I am not sure multi-threading works as you expect. You need multiple cores / processors for this to be effective and is a deep topic in itself.

answered Sep 16 '14 at 15:41

Belogix

8,129
1
27
32

I tried that but I ended up with a Too many sessions error :-( – Steven Wood Sep 16 '14 at 15:42
Sure, but the opening line says it all "SQL CE objects are not thread-safe" - as soon as you start sharing over threads you WILL run into a problem sooner or later. You might need to create a pool or throttling but do not share... – Belogix Sep 16 '14 at 15:43

score 0 · Answer 2 · answered Sep 16 '14 at 15:44

0

Ensure IDENTITY_INSERT is ON for the table.

answered Sep 16 '14 at 15:44

David W

10,062
34
60

Cant insert into Identity Column when Multithreading

2 Answers2