All geek questions in one place

MYSQL - Lock - InnoDB

I am using mysql with InnoDB databases.

If all my transactions are attachments and selections (no updates), I assume that I don’t have to worry about SQL locking.

I do not see a scenario for a deadlock. Am I right in assuming that deadlock cannot happen if I only do insertions and selections?

It may not be relevant, but all transactions are performed with PDO

+4
source share
2 answers

Not. You still have to worry about SQL locking.

You can get deadlocks even in the case of a transaction that inserts one row. This is because the insert operation is not atomic, and locks are set automatically in (possibly several) index records of the inserted row.

+1
source

The InnoDB MySQL storage engine has row-level locks, while the MySQL MyISAM storage engine has table-level locks. MyISAM simply locks entire tables and does not support transactions, so it is not possible to have locks at the database level. Please note that an application can block another application while sitting on a table lock in a table that they are both trying to access, but this is a code error, not a db deadlock.

InnoDB supports transactions and has row-level locks, so db-level deadlocks are possible (and sometimes they can occur on a busy system, so you need to encode them). Many of what MySQL will call “deadlocks” are not “true deadlocks” because they are the result of slow UPDATEs, causing other timing queries to lock rows.

0
source

Source: https://habr.com/ru/post/1393676/

More articles:


All Articles