flutter: sqflite database getting locked though using transaction on Android

Question

I have native Android application which send Map() data to embedded flutter module every 50ms through EventChannel, and flutter module add received data to Database (flutter module has no UI).

In android activity, pressing a button will send the data to the module every 5ms in a minute.

Android Activity

public class MainActivity extends FlutterActivity implements View.OnClickListener {
    private static final String ENGINE_ID = "engine_id";
    private static final String CHANNEL_EVENT = "com.example.eventchannel";
    private FlutterEngine flutterEngine;
    private EventChannel eventChannel;
    private EventChannel.EventSink eventSink;

    private Button button;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        flutterEngine = new FlutterEngine(this);
        flutterEngine.getDartExecutor().executeDartEntrypoint(DartExecutor.DartEntrypoint.createDefault());
        FlutterEngineCache.getInstance().put(ENGINE_ID, flutterEngine);

        eventChannel = new EventChannel(flutterEngine.getDartExecutor().getBinaryMessenger(), CHANNEL_EVENT);
        eventChannel.setStreamHandler(new EventChannel.StreamHandler() {
            @Override
            public void onListen(Object arguments, EventChannel.EventSink events) {
                eventSink = events;
            }

            @Override
            public void onCancel(Object arguments) {
                eventSink = null;
            }
        });

        button = findViewById(R.id.btn_send);
        button.setOnClickListener(this);
    }

    @Override
    public void onClick(View view) {
        if (view.getId() == R.id.btn_send){
            Handler handler = new Handler(Looper.getMainLooper());
            handler.post(() -> {
                int i = 0;
                MapData mapData = new MapData();
                while (i < 1200){
                    try {
                        Thread.sleep(50);
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }

                    if (eventSink != null){
                        eventSink.success(mapData.createMap());
                    }
                    i++;
                }
            });
        }
    }
}


public class MapData {
    public Map<String, Object> createMap(){
        return new HashMap<String, Object>(){
            {
                put("name", "aaaaaa");
                put("age", "aaaaaa");
                put("gender", "aaaaaa");
                put("birthday", "aaaaaa");
                put("phone", "aaaaaa");
                put("address", "aaaaaa");
            }
        };
    }
}

And flutter module insert received data to Database.

pubspec.yaml

dependencies:
  flutter:
    sdk: flutter

  cupertino_icons: ^1.0.2
  sqflite: ^2.0.2
  path_provider: ^2.0.9
  synchronized: ^3.0.0+2

main.dart

void main() async {
  WidgetsFlutterBinding.ensureInitialized();
  DatabaseHelper databaseHelper = DatabaseHelper.instance;
  FlutterEventChannel.instance.configureChannel(databaseHelper);
}

Database Helper Class

class DatabaseHelper {
  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  static Database? _database;
  final _dbLock = Lock();

  Future<Database> get database async {
    if (_database != null) {
      return _database!;
    } else {
      await _dbLock.synchronized(() async {
        _database ??= await _initDatabase();
      });
      return _database!;
    }
  }

  _initDatabase() async {
    Directory documentDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentDirectory.path, 'sample');

    return await openDatabase(path, version: 1, onCreate: _onCreate);
  }

  Future _onCreate(Database db, int version) async {
    await db.execute('''
    CREATE TABLE ${DbDefines.tripTable} (
    $'_id' INTEGER PRIMARY KEY,
    $'name' TEXT NOT NULL,
    $'age' TEXT,
    $'gender' TEXT,
    $'birthday' TEXT,
    $'phone' TEXT,
    $'address' TEXT
    )
    ''');
  }

  Future<void> insert(String table, Map<String, dynamic> row) async {
    Database db = await instance.database;
    await db.transaction((txn) async {
      await txn.insert(table, row);
    });
  }
}

EventChannel Class

class FlutterEventChannel {
  late DatabaseHelper databaseHelper;
  static const channelName = 'com.example.eventchannel';
  late EventChannel _eventChannel;
  late StreamSubscription _streamSubscription;

  static final FlutterEventChannel instance = FlutterEventChannel._init();
  FlutterEventChannel._init();

  void configureChannel(DatabaseHelper databaseHelper) {
    this.databaseHelper = databaseHelper;

    _eventChannel = const EventChannel(channelName);
    _streamSubscription = _eventChannel.receiveBroadcastStream().listen(
      (event) {
      databaseHelper.insert(
          'sample', Map<String, dynamic>.from(event));
      },
      onError: (error) {}, cancelOnError: true);
  }
}

Then pressing the button once and the transmission is successful, but an error occurs in the middle of the second time.

I/flutter: Warning database has been locked for 0:00:10.000000. Make sure you always use the transaction object for database operations during a transaction

Now I'm confused because I can't find any solution... Any help would be appreciated!

Answer 1

I fixed java code as below and it worked.

Handler handler = new Handler(Looper.getMainLooper());
Runnable runnable = new Runnable() {
    @Override
    public void run() {
        if (eventSink != null){
            eventSink.success(new MapData().createMap());
            handler.postDelayed(this, 50);
        }
    }
};
handler.post(runnable);