Start a new topic

Parallel.Foreach and VistaDB

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'm going to assume you've simplified the problem down so some of this feedback may not be valid (since it may not apply to your full example).  My first question is why are you doing the explicit transaction?  A single DML statement in VistaDB is implicitly a transaction, but when wrapped in an explicit transaction the engine has to do a lot more work under the covers - about double the work - and it notably increases the locking effort.  If your data updates don't really require a transaction to ensure atomicity between multiple DML statements I'd recommend removing the explicit transaction and see how that changes the results you're seeing.

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 ?


 

Sure.  We'll have to evaluate whether this is a transient error that shouldn't be thrown or a case where we're encountering a lock timeout and just not throwing the correct exception.  We'll dig into it.

Login to post a comment