I'm trying to understand how EF Core transaction with a lot of inner commands use memory.

Let's say I have code like this:

using var reader = new MyReader(myStream);
using var context = new BloggingContext();
using var transaction = context.Database.BeginTransaction();

try
{
    while (!reader.EndOfStream()) 
    {
        var myObj = reader.ReadNextObject();
        context.Database.ExecuteSqlRaw("INSERT INTO [MyTable] ([Col1], [Col2]) VALUES ({0}, {1})", 
                                       myObj.prop1, myObj.prop2);
    }

    transaction.Commit();
}
catch (Exception)
{
    // Exception handling
}

MyReader is reading a very large (millions of rows) collection of records streamed from some source.

My questions are:

1. Can objects referenced by myObj variable be garbage collected before we commit the transaction, or are we effectively loading all of them into memory?

2. Are all SQL commands we set to execute stored in memory until we commit the transaction, or are they sent to the database immediately?

3. Am I understanding correctly, that this would lock [MyTable] until we commit the transaction?

CodePudding user response：

Can objects references by myObj variable be garbage collected before we commit the transaction

Yes. Once ExecuteSqlRaw completes, EF will not hold any references to the object that myObj refers to, and it can be collected.

Are all SQL commands we set to execute stored in memory until we commit the transaction, or are they sent to the database immediately?

They are sent to the database immediately by ExecuteSqlRaw, the row is inserted and transaction log records written to the in-memory log buffer. The transaction just prevents the statement from waiting until the log records are hardened to disk, and allows you to roll back the whole transaction. And even the log records are not kept in memory on the server; they will be hardened to the log files asynchronously, and read back from there in the case of a rollback.

Am I understanding correctly, that this would lock [MyTable] until we commit the transaction?

SQL Server will lock only the inserted rows, unless you inserted so many that it triggered lock escalation.