Access DB Transaction Insert limit

问题:

Is there a limit to the amount of inserts you can do within an Access transaction before you need to commit or before Access/Jet throws an error?

I'm currently running the following code in hopes to determine what this maximum is.

OleDbConnection cn =
                new OleDbConnection(
                    @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\myAccessFile.accdb;Persist Security Info=False;");
            try
            {
                cn.Open();
                oleCommand = new OleDbCommand("BEGIN TRANSACTION", cn);               
                oleCommand.ExecuteNonQuery();    
                oleCommand.CommandText =
                            "insert into [table1] (name) values ('1000000000001000000000000010000000000000')";

                for (i = 0; i < 25000000; i++)
                {
                    oleCommand.ExecuteNonQuery();
                }                

                oleCommand.CommandText = "COMMIT";
                oleCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

            }
            finally
            {
                try
                {

                    oleCommand.CommandText = "COMMIT";
                    oleCommand.ExecuteNonQuery();                    
                }

                catch{}

                if (cn.State != ConnectionState.Closed)
                {
                    cn.Close();
                }
            }

The error I received on a production application when I reached 2,333,920 inserts in a single uncommited transaction was: "File sharing lock count exceeded. Increase MaxLocksPerFile registry entry". Disabling transactions fixed this problem.

回答1:

Yes, there is in fact a limit. And you've reached it clearly. Per Microsoft's documentation:

The error occurs if the number of locks required to perform a transaction exceeds the maximum number of locks per file.

At that link there are a couple of workarounds. The second one is more realistic, it changes the max locks temporarily.

回答2:

I just successfully created and committed a transaction of 5,000,000 inserts on a table in an .accdb database. One significant difference is that I did not issue BEGIN TRANSACTION and COMMIT statements directly, I used an OleDbTransaction object instead:

cmd.CommandText =
    @"INSERT INTO Companies (ID, CompanyName) VALUES (?, ?)";
cmd.Parameters.Add("?", OleDbType.Integer);
cmd.Parameters.Add("?", OleDbType.VarWChar, 255);
cmd.Prepare();

OleDbTransaction tran =  con.BeginTransaction();
cmd.Transaction = tran;
for (int i = 1; i <= 5000000; i++)
{
    if ((i % 100) == 0)
    {
        Console.WriteLine(i.ToString());
    }
    cmd.Parameters[0].Value = i;
    cmd.Parameters[1].Value = "Company" + i.ToString();
    cmd.ExecuteNonQuery();
}
tran.Commit();

Edit re: comments

After successfully creating and committing a 10,000,000-insert transaction, and verifying that a .Rollback() does actually work (good suggestion, neoistheone!), I checked the IsolationLevel of the transaction. The default IsolationLevel for the ACE.OLEDB driver is apparently ReadCommitted, which according MSDN means:

Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.

Wikipedia has a good description of those different "read phenomena" here.

Perhaps if I was to specify a more strict isolation level (like Serializable) I might hit the lock limit described in the question, although since these are all INSERT operations maybe the ACE.OLEDB driver just has to "hide" the inserts from all other connections until they are committed.

Edit re: performance

Since a fairly common reason for wrapping a large number of INSERT operations in a transaction is to get them done faster, I tested a batch of 100,000 inserts with and without the OleDbTransaction. (The prepared statement was used in both cases.) Without the OleDbTransaction the batch took approximately 36 seconds to complete. Enabling the OleDbTransaction with the default isolation level (ReadCommitted) reduced the elapsed time to around 23 seconds.