SQL: How can you release or prevent update/insert locks while remaining in the transaction?

Question

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.

Answer 1

We don't use transactions

Yes you do.

We handle locking of our fields/data ourselves

No you don't.

We've noticed that if we run our code in a transaction, it speeds up dramatically

Your code always runs in a transaction. By default, a SQL statement that isn't part of a START TRANSACTION; ... COMMIT; block will implicitly start such a block. That implicit block will automatically commit (thereby releasing its locks) at the end of the statement.

If you run more than one SQL statement "at one time", each of them will individually autocommit by default. If you run more than one SQL statement in one explicit transaction, all the SQL statements will commit "at the same time". That might have something to do with speed difference you see.

This is not a MySQL thing. It's a SQL thing. It's part of the SQL standard.

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?

You can't disable locks on any SQL dbms.

You can't release locks from inside a transaction without either commiting the transaction or rolling it back.

---

MySQL docs

Answer 2

For "Part 1", avoid DELETE as a way to clear a table...

Plan A: Instead, do TRUNCATE TABLE t.

Plan B: Use this sequence:

1. CREATE TABLE t_new LIKE t;
2. Insert the values into t_new.
3. `RENAME TABLE t TO t_old, t_new TO t;
4. DROP TABLE t_old

Plan A avoids locking every row of the table. Plan B avoids any downtime for access to the table by atomically swapping in the new data.

(As for turning off transactional semantics -- not possible in InnoDB. MyISAM has no such functionality; instead, it locks entire tables, which mya lead to worse problems.)