Database locked after update 2.19 > 2.20

[kuhnroyal]

Hi @simolus3, after updating from 2.19 to 2.20 my gets stuck on parallel write operations.

I am using Future.wait to start parallel downloads of data which afterwards gets inserted into the database via separate parallel transactions. This completely locks the database since 2.20.0.

The transaction never complete, after a hot restart there are database locked errors.
Same thing happens in release builds.

I am using a drift isolate setup that has worked perfectly for a some time now.
Do you have an idea on what could cause this?

[simolus3]

Thanks for the report, I'll try to reproduce this. The only recent change that comes to mind was transactions obtaining the lock before invoking the callback (previously the first statement in the transaction block would open the transaction), but that has landed in 2.19.0 already. I wonder if the isolate changes in 1d296a4 might be related to this. Are the parallel transactions opening other nested transactions as well or is it just a single level?

[kuhnroyal]

There are no nested transactions, from stepping through the debugger, it looks like this:

• T1 starts
• query/insert some data in T1 -> success
• T2 begins (transaction() is reached)
• query/insert more data in T1 -> hangs forever
• T2 never actually enters

[kuhnroyal]

@simolus3 Yea the 1d296a4 commit is the culprit. With the parent commit it still runs.

And it doesn't seem to be in 2.19.x as far as I can tell.

[simolus3]

I can't reproduce the problem like this, which sounds like what you're describing right?

  db.transaction(() async {
    print('First transaction: Start');
    await db.customSelect('SELECT 1').get();

    for (var i = 0; i < 5; i++) {
      print('First transaction, query $i');
      await db.customSelect('SELECT 1').get();
      print('First transaction, query $i end');
      await Future.delayed(const Duration(seconds: 1));
    }
  });

  await Future.delayed(const Duration(seconds: 1));

  db.transaction(() async {
    print('Second transaction: Start');
    await db.customSelect('SELECT 1').get();
    print('Second transaction: Done');
  });

However, if I spawn another nested transaction in the first one, I can reproduce the deadlock:

  db.transaction(() async {
    print('First transaction: Start');
    await db.customSelect('SELECT 1').get();

    for (var i = 0; i < 5; i++) {
      print('First transaction, query $i');
      await db.customSelect('SELECT 1').get();
      print('First transaction, query $i end');
      await Future.delayed(const Duration(seconds: 1));
    }

    print('trying nested');
    await db.transaction(() async {
      print('nested'); // never called
    });
  });

  await Future.delayed(const Duration(seconds: 1));

  db.transaction(() async {
    print('Second transaction: Start');
    await db.customSelect('SELECT 1').get();
    print('Second transaction: Done');
  });

I'll fix that, maybe that also solves your issue.

[simolus3]

Can you try checking whether f781aa3 fixes this issue?

[kuhnroyal]

Yes that fixes it!

[simolus3]

Thanks for the confirmation, I've released a patch release containing the fix as 2.20.3.