Hi and thanks for creating a great product that we are evaluating these days.

We have run into an issue that happens now and then related with our web service. I have reproduced the error by using the .net "Parallel.ForEach".

When not running that many parallel db updates (less then 10) it works, with really bad performance. Increasing the parallel db updates it will get an "..The process cannot access the file because another process has locked a portion of the file."

 

VistaDBConnectionStringBuilder vcb = new VistaDBConnectionStringBuilder(accountconfig.ConnectionString)
            {
                Pooling = true,
                OpenMode = VistaDBDatabaseOpenMode.NonexclusiveReadWrite,
                TransactionMode = VistaDBTransaction.TransactionMode.On

            };
            Parallel.ForEach(items, intitem =>
            {
                using (var conn = new VistaDBConnection(vcb.ToString()))
                {
                    conn.Open();

                    using (var dbtrans = conn.BeginTransaction())
                    {

                        using (VistaDBCommand command = new VistaDBCommand())
                        {
                            command.Connection = conn;
                            command.Transaction = dbtrans;
                            command.CommandText =
                                string.Format(
                                    "INSERT INTO {0} (intnewslettersubscriberid,intproductid) VALUES ({1},{1})", tablename,
                                    intitem);
                            command.ExecuteNonQuery();
                        }
                        dbtrans.Commit();
                    }
                    conn.Close();
                }
            });

 

Please advise use VistaDB using Parallel.Foreach and how to increase performance on Parallel.Foreach.

(There are really many lock-files being created on db update process.)

Thanks and happy new year to you all :)

I have simplified the application code for easier understanding the problem. The problem is also real for this example.  The real application code does not have a single insert statement, but my example has.

I'm aware of the extra overhead for transactions, and know this may impact performance. The major issue here is the error message generated by VistaDB, is this something you can help us with ?