For example, I lock some rows: c2 is not indexed. It means MySQL has to search entire table and if it read uncommitted or read committed isolation levels it places locks on every scanned row and if it doesn’t satisfy the WHERE condition it immediately release the lock. If it is repeatable read those locks that do not satisfy the
Tag: innodb
MariaDb InnoDB deadlock while doing many inserts
I’m having deadlock issues with a MariaDB (10.0.27)if it’s under pressure. The database schema is basically a hierarchy and the leaf node causes this: This is how my leaf table looks like: I’m able to reproduce the deadlock with an input set that puts the system under pressure. With this, I’ve been playing around and once I remove the unique
How to know if an uncommitted transaction tries to insert a specific unique key into SQL
I’m writing a programm which inserts data to a MariaDB-Server and can be used by different people on the same time. The transactions take some time, so the following problem might occur: Person A …
Update large table from smaller, mission critical, table without locking small table
In MySQL, I have two innodb tables, a small mission critical table, that needs to be readily available at all times for reads/writes. Call this mission_critical. I have a larger table (>10s of …
Optimize MySQL InnoDB query for max, count
I have an MySQL InnoDB table with 5.7M rows and 1.9GB size: +——————-+———+——+—–+———+—————-+ | Field | Type | Null | Key | Default | Extra …
Suggestion for a contact management database design
I am currently designing a contact management database for a chamber of commerce. The goal of the database is to store all the person (except our own staff), all recorded companies (regular companies and member of the chamber), addresses of the person and companies, tasks that the staff are currently responsible for, a list of our staff (user) and the
effective innodb_lock_wait_timeout value check
I have a table with lakhs of rows. Now, suddenly I need to create a varchar column index. Also, I need to perform some operations using that column. But its giving innodb_lock_wait_timeout exceeded …