Wiktor Wiktor - 17 days ago 3
C# Question

SQL queries with Create index, table, procedure runing from parallel threads

I've got problem with queries executed from parallels threads.
Almost always I am getting error about that index | table type | procedure | table is already exists.

I am pretty sure that my sql query is protected against that error and it should never appear.

This query should be able to run from concurrent thread without any error.

What I am doing:


  1. I've got hundreds of files, each file contains couple millions lines.

  2. Each line contains object which I need to insert into specific table.

  3. If table/index/procedure not exists I need to create it.

  4. Currently this is working pretty good except this errors which should not appear.



My code with query (with index creating ):

public void CreateIndex(string tableName)
{
try
{
StringBuilder sb = new StringBuilder();

var connectionString = ConnectionString;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();

// Creating INDEX
sb.AppendFormat(@" IF NOT EXISTS(SELECT * FROM sys.indexes
WHERE name='TimeSeries_DateStamp_{0}' AND object_id = OBJECT_ID('{0}'))
exec(' CREATE UNIQUE CLUSTERED INDEX TimeSeries_DateStamp_{0} ON [dbo].[{0}]
(
[TimeSeriesID] ASC,
[DateStamp] ASC
) WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY] '); ", tableName);

using (SqlCommand cmd = new SqlCommand(sb.ToString().Trim(), conn))
{
cmd.ExecuteNonQuery();
}

conn.Close();
}

}
catch (Exception ex)
{
Log.Error(string.Format("Error occured while adding index: {0}", tableName), ex);
}


}

I've marked (...) code where there is really nothing important happening - just some data processing.

DB is Entity Framework 6 context also containing this CreateIndex method.

Parallel.ForEach(timeSeriesFiles, file =>
{
(...)

// FOREACH LINE
foreach (string line in File.ReadLines(file))
{
(...)
using (var db = Context.DB)
{
db.CreateIndex(tableName(line));
}
(...)
}
}


I would be grateful for any advice or clue why is this happening.

Answer

You are receiving this error because at least 2 threads are trying to create the index/SProc/Table at the same time. I don't see anything in your SQL that would prevent this error. I would suggest implementing some sort of locking mechanism to handle this error. You can do this in SQL, or in your code - either way, the logic is basically the same:

  1. Check if index exists. If index does not exist, obtain a lock. In sql, something like: SELECT 1 FROM tableName WITH (TABLOCKX)
  2. Once lock is granted, check if index does not exist (yes, you need to check twice. Perhaps another thread came though and created the index while you were waiting on being granted the lock).
  3. Release lock