0

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.

11
  • I have seen this question once already today?
    – RiggsFolly
    Feb 5, 2020 at 13:12
  • It was closed for some reason? Because apparently it was asking for "recommendations"? I'm not asking for recommendations though, just asking if SQL somehow allows this. Anyway to reopen the question, I've deleted the previous one and created a new one, with different questions to not imply I'm asking for recommendations, but rather a technical question about SQL itself.
    – patrick_vane
    Feb 5, 2020 at 13:14
  • I would guess, as thats all I can do, as we know nothing about your We handle locking of our fields/data ourselves that your locking techniques and the one's used by MySQL are whats getting in your way. Do you have a better description/example of your personall locking methods
    – RiggsFolly
    Feb 5, 2020 at 13:17
  • I'm not sure if it's relevant, we just want to disable or unlock updates/inserts (SQL locks), it was just to provide a reason/context to the question. But anyway if you're interested, our locking system is similar to holding locks on redis. We lock on string keys, with read or write access (read allowing multiple reads at once, write only allowing 1 at once). To be more specific we don't use redis, since it was inefficient, we implemented our own java server and manage locks with that, but the result is the same.
    – patrick_vane
    Feb 5, 2020 at 13:23
  • So your locks dont actually lock the database at all, its a logical not physical lock like a semaphore
    – RiggsFolly
    Feb 5, 2020 at 13:25

2 Answers 2

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

17
  • I see what you mean. We do however handle locking ourselves as well, so it's being done twice basically. Either way, if we can't disable/prevent locking or release them within a transaction, it means we can't put everything inside of one transaction, meaning we can't have the performance improvement from doing that? There's no way around it? Feb 5, 2020 at 15:57
  • @patrick_vane: I'm not sure I understand your question. Edit your original question above, and paste a minimal version of your locking code, minimal create table... and insert ... statements that everyone here can use to troubleshoot your problem. Feb 5, 2020 at 17:02
  • I've added an example to the original post, please let me know if that example is understandable/comprehensible. Feb 5, 2020 at 18:13
  • 1
    @patrick_vane Maybe it would be possible to modify your script to update rows only once. Also your description looks like you probably use "cursor based" approach and maybe it has a sense to rewrite your scripts using "set based" one. And the fact you are facing with locking/deadlocking problem can mean that you may get different results when using explicit transactions (it's hard to say for sure without knowing the whole picture). Feb 6, 2020 at 12:12
  • 1
    @patrick_vane I mean maybe it's possible to combine your scripts or queries inside the script to run one big update for multiple rows instead of execution of dozens of separate queries. Feb 7, 2020 at 10:44
0

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.)

2
  • Often times it's not the whole table that has to be removed. In this example it could be, but we also have 'objects' and 'users', that in the database have an 'id', 'index' and 'value' then. In that case, we want to only remove the 'indexes'/'values' of a given 'id' then. Because of this, we can't delete/clear/truncate the whole table. Feb 10, 2020 at 20:08
  • For deleting a few ids, there should be no problem. For deleting a large part of the table see other techniques in mysql.rjweb.org/doc.php/deletebig
    – Rick James
    Feb 11, 2020 at 5:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.