Concurrent Access and Locking

1. A proper locking mechanism is necessary to ensure data consistency when there is a possibility of multiple clients accessing and possibly modifying the same data at the same time.
2. table-level locks, page-level locks, and row-level locks
3. MyISAM and MEMORY storage engines can only work with table-level locks. InnoDB supports row-level locks, and Berkeley DB supports page-level locks.
4. MyISAM - If the insert operation results in writing the record at the end of the datafile, reading can be done without a lock.
5. InnoDB Locking
There are two types of row-level locks: shared and exclusive.
InnoDB also uses so-called intension locks on a table. There are also two types of intension table locks, shared and exclusive.
Rocord locking
What InnoDB actually locks is the index entry, the space before it, and the space after the last record. This method is called next-key locking.
The next-key locking is necessary to avoid the phantom row problem in transactions. If we did not lock the space before the record, it would be possible for another transaction to insert another record in between. Thus, if we were to run the same query again, we would see the record that was not there the first time we ran the query. This would make it impossible to meet the requirement of the serializable read transaction isolation level.
InnoDB has an automatic deadlock detection lagorithm. It will usually roll back the last transaction involed in a deadlock.  Additionally, some transactions may be considered to be in a virtual deadlock. For example, if a query is written is such a way that it examines several billion records, it may not release its locks for weeks, although from a theoretical point of view it eventually will. For such situations InnoDB uses a lock timeout, which is controlled by the configuration variable innodb_lock_wait_timeout.

 Accessing records always in the same index order, writing properly optimized queries, and commiting transactions frequently are some of techniques that help prevent potential deadlocks.