MySQL Development advanced Article Series 13 lock problem (about table lock, deadlock example, lock wait setting)

Tag: is set ODB full ROM wait ima margin fallback

I. When to use a table lock

For InnoDB tables, row locks should be used in most cases. In individual special transactions, you might consider using table locks (recommended).
1. Transactions need to update most or all of the data, the table is larger, the default row lock not only makes the transaction inefficient, may cause other transactions for a long time lock wait and lock conflict, this situation consider using table locks to improve the execution speed of the transaction (with my experience in SQL Server, the large table has 100w, Deleting 40w, table locks can sometimes cause long periods of unfinished execution. Or use batches to perform well).
2. Transactions involving multiple tables are more complex and are likely to cause deadlocks, resulting in a large number of transaction rollbacks. In this case, consider a one-time locking of the table involved in the transaction, avoid deadlocks, and reduce the cost of the database due to transaction rollback.
Note two points using table locks
(1) Lock tables Although the InnoDB can be added to the table lock, but the table lock is not managed by the InnoDB storage engine layer, it is the upper MySQL server responsible. Only when Autocommit=0, Innodb_table_locks=1 (the default), does the INNODB layer know that the table lock is added to MySQL, and MySQL server can sense the row lock added by InnoDB.
(2) Lock tables to the InnoDB table to be aware that the autocommit is set to 0, otherwise MySQL will not lock the table, before the end of the transaction, do not use unlock tables to release the table lock, because it will implicitly commit the transaction. Commit or rollback does not release a table lock added with lock tables. The table lock must be released with unlock tables.

The following in the 5.7 version of the database, Session 2 will also block, as described above is not blocked, because session 1 is not set autocommit =0 ( later in the argument )

-- Session 1 Adds a table lock read to city  without setting set  autocommit =0read  

  --   Session 2 will block UPDATESET citycode='005' WHERE city_id=103  

  -- Session 1 Commit COMMIT  --  session 1 release table lock UNLOCK TABLES;

Two. About Deadlocks

In MyISAM, the table lock is used, and when all required locks are obtained, either all are satisfied, or wait, so there is no deadlock. A deadlock example is shown below in InnoDB:

/tr>

Session 1	Session 2
SET autocommit =0 SELECT * from city  WHERE city_id=103 for UPDA TE;	SET autocommit =0 SELECT * from citynew  WHERE city_id=103 for UPDATE;
--because session 2 has acquired an exclusive lock, the statement waits for  select * from citynew  WHE RE city_id=103 for UPDATE;
	--deadlock  select * FRO M city  WHERE city_id=103 for UPDATE; Error code: 1213 Deadlock found when trying to get lock; try restarting transaction

In the above case, two transactions need to acquire an exclusive lock held by each other to continue the transaction, which is a typical deadlock. After a deadlock occurs, InnoDB automatically detects and causes one transaction to release the Lock and rollback (rollback), and another transaction to lock the transaction.

Three. Lock pending view

Involving an external lock or a table lock, InnoDB does not automatically detect the deadlock, which requires setting the lock wait timeout parameter innodb_lock_wait_timeout to resolve (the settings need to be cautious), this parameter is not only to solve the deadlock problem, in the concurrency, A large number of transactions cannot be suspended immediately with the required locks, will consume a lot of resources, or even drag across the database (default is 1 in SQL Server is always waiting).

--   The following is 5 seconds  to get a lock on the timeout like 'innodb_lock_wait_timeout';

MySQL Development advanced Article Series 13 lock problem (about table lock, deadlock example, lock wait setting)