Shulou(Shulou.com)06/01 Report--

Deadlocks are a classic problem with transactional database systems, but they are not dangerous unless they are so frequent that you cannot handle a few transactions at all. When a rollback occurs due to a deadlock, you can usually just reissue a transaction in your application.

InnoDB uses automatic row-level locking. You might happen to have a deadlock when inserting or deleting a single record. This is because these operations are not truly "atomic": they automatically lock index records (there may be several) on inserted/deleted rows.

The following techniques can be used to cope with deadlocks or reduce the number of deadlocks:

Use SHOW INNODB STATUS in MySQL >=3.23.52 and>= 4.0.3 to determine what caused the last deadlock. This can help you tune your application to avoid deadlocks.

Always be prepared to reissue a transaction if an error occurs due to deadlock. Deadlocks are not dangerous. Just try again.

Submit your business frequently. Small transactions have less collision potential.

If lock read SELECT is used... FOR UPDATE or... LOCK IN SHARE MODE, try to use a lower isolation level READ COMMITTED.

Access your tables and records in a fixed order. Such transactions will form a finer queue and avoid deadlocks.

Add an appropriate index to your table. Then your query needs to scan fewer indexes and therefore set fewer locks. Use EXPLAIN SELECT to determine the appropriate index MySQL picked for your query.

Minimize locking: If you can get the data you want in an older snapshot with a SELECT, don't add the FOR UPDATE or LOCK IN SHARE MODE clause. It is a good idea to use the READ COMMITTED isolation level at this point, because each consistent read in the same transaction reads only the snapshot of data it first identified.

If there is still no remedy, serialize your transactions using table-level locking: LOCK TABLES t1 WRITE, t2 READ,... ; [do something with tables t1 and t2 here]; UNLOCK TABLES。Table-level locking allows you to form fine queues of transactions. Note that LOCK TABLES implicitly starts a transaction, just as the command BEGIN, UNLOCK TABLES implicitly ends a transaction, just as COMMIT.

Another solution to serialize transactions is to create an auxiliary "semaphore" table with only one row. Each transaction updates this record before accessing other tables. In this way all transactions will continue to be executed. Note that the InnoDB real-time deadlock detection algorithm is also working because the serializing lock is a row lock. In MySQL we have to timeout for table-level locking.

Deadlock detection and rollback

InnoDB automatically detects deadlocks in a transaction and rolls back one or more transactions to prevent deadlocks. Starting with version 4.0.5, InnoDB will try to extract small transactions for rollback. The size of a transaction is determined by the number of rows it inserts, updates, and deletes. Previous to 4.0.5, InnoDB always rolled back the transaction whose lock request was the last one to build a deadlock, that is, a cycle in the waits-for graph of transactions.

InnoDB cannot detect deadlocks caused by MySQL's LOCK TABLES statement, or deadlocks caused by locks in other table types. You have to fix these situations by setting innodb_lock_wait_timeout in my.cnf.

When InnoDB performs a full rollback of a transaction, all locks that the transaction has attached are released. However, if a one-sentence SQL statement rolls back because it returns an error, the lock set by that SQL statement may be retained. This is because InnoDB r's row lock storage format does not know which SQL statement set the lock.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.