Current situation:

1. We're using mysql (percona) 8.0, with InnoDB tables
2. We're using read-committed transaction isolation, although we can switch to read-uncommitted because:
3. We don't use transactions or rollback anywhere (we always store our data with the last statement, if the last statement isn't executed, the data inserted is all removed/garbage-collected automatically over time)
4. We handle locking of our fields/data ourselves (outside of the database, it's completely separated from the database, think: redis locks), to prevent any race conditions

The problem:

We've noticed that if we run our code in a transaction, it speeds up dramatically (x5, for example, a large script/action goes down from 7500ms to 1500ms, timed in PHP). The downside is that it seems that updates (and inserts too?) create a lock, which normally (when not in a transaction) releases immediately, but in a transaction, it keeps the lock until the transaction is committed. This causes a lot of deadlocks, since the order of updates can't ever be guaranteed in our system, plus it conflicts with our own locking system.

We've tried to lower the transaction isolation level down to read-uncommitted, but it still creates locks that don't get released until the transaction is over.

Question:

Is there a way to disable locks generated by SQL (whether it's with MySQL+InnoDB or with any other database or table type), or is there a way to release those locks immediately while remaining in the transaction?

Example:

Our table:

CREATE TABLE `data_global_field_2` (
  `index` bigint(20) NOT NULL,
  `value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `data_global_field_2`
  ADD PRIMARY KEY (`index`);

Part 1:

Script 1:

$db->exec("DELETE FROM \"data_global_field_2\"");
sleep(6); // this sleep just makes it easier to make this race condition happen
$db->exec("INSERT INTO \"data_global_field_2\" (`index`, `value`) VALUES (1,'one'), (2,'two'), (3,'three'), (4,'four'), (5,'five')");

Script 2:

$db->exec("INSERT INTO \"data_global_field_2\" (`index`, `value`) VALUES (1,'something') ON DUPLICATE KEY UPDATE `value` = 'something'");

Now, in this case, if script 1 and 2 are being run, script 2 will make a modification, and script 1 will error/fail. As far as I know, we can't make a SQL lock on non-existing rows.

Part 2:

We could use a semaphores table, but that would be the same as locking it with something external. So, because of this, we wrap it in our own lock, like so:

Script 1:

Synchronizer::syncEx('some key', static function () use (&$db)
{
    $db->exec("DELETE FROM \"data_global_field_2\"");
    sleep(6); // this sleep just makes it easier to make this race condition happen
    $db->exec("INSERT INTO \"data_global_field_2\" (`index`, `value`) VALUES (1,'one'), (2,'two'), (3,'three'), (4,'four'), (5,'five')");
});

Script 2:

Synchronizer::syncEx('some key', static function () use (&$db)
{
    $db->exec("INSERT INTO \"data_global_field_2\" (`index`, `value`) VALUES (1,'something') ON DUPLICATE KEY UPDATE `value` = 'something'");
});

Then these two scripts would be executed one after another, which would work.

Part 3:

Now, this happens in our internal code, these actions, where a table (or part of it) is being cleared, and set to something else. Basically, multiple-query actions. For example, script 1 is executed as:

$GLOBAL->fields['Strings']->set(['one', 'two', 'three', 'four', 'five']);

Now, if we run 2 scripts, like this:

Script 1:

$GLOBAL->fields['Strings']->set(['aaa', 'bbb']);
sleep(3);
$GLOBAL->fields['Strings']->set(['bbb', 'ccc']);
sleep(3);
$GLOBAL->fields['Strings']->set(['ccc', 'ddd']);

Script 2:

$GLOBAL->fields['Strings']->set(['aaa', 'bbb', 'ccc']);

Then this would work, since it would always grab one of our own locks before it runs any SQL, so any delete+insert action is isolated from the other deletes/inserts.

Part 4:

But, if we can put all of this in a transaction, then it would be many times faster, like this:

Script 1:

$db->transaction(static function () use (&$GLOBAL)
{
    $GLOBAL->fields['Strings']->set(['aaa', 'bbb']);
    sleep(3);
    $GLOBAL->fields['Strings']->set(['bbb', 'ccc']);
    sleep(3);
    $GLOBAL->fields['Strings']->set(['ccc', 'ddd']);
});

Script 2:

$db->transaction(static function () use (&$GLOBAL)
{
    $GLOBAL->fields['Strings']->set(['aaa', 'bbb', 'ccc']);
});

But, because it's in a transaction then, they will keep holding the SQL locks (most noticeably script 1 in this case). This will then cause script 2 to have our own lock, but then it also needs the SQL lock, but since script 1 still has the SQL lock, it can't get that. And since script 1 wants our own lock, which script 2 has, it too will be stuck. It will be deadlocked.

I hope this example makes sense.