Tag Info

New answers tagged

1

Same scenario here. I have been unable to find a definition of Lock_time in the slow queries log, but this post provides a definition consistent with the situation: the “lock time” logged in the slow query log only counts time for table-level locks that are taken at the MySQL top level, not InnoDB locks taken at the storage-engine level. I found ...


2

I have heard of concurrency problems like that in MySQL before. Not so in Postgres. Built-in row-level locks in the default READ COMMITTED transaction isolation level are enough. I suggest a single statement with a data-modifying CTE (something that MySQL also doesn't have) because it's convenient to pass values from one table to the other directly (if you ...


0

Do not use LOCK TABLES with InnoDB unless you have a very special need. Instead, learn about "transactions" and use BEGIN...COMMIT to effect most "locking" needed. See also SELECT ... FOR UPDATE....


3

It sounds like the code might be using SAVEPOINTs to handle errors, and not releasing the savepoints before proceeding. That would explain the large number of virtual xid locks. RELEASE SAVEPOINT after you're done with a step. You might also want to consider batching the work into smaller chunks, as the: SAVEPOINT Try it ROLLBACK TO SAVEPOINT if it ...



Top 50 recent answers are included