Locking in MySQL

• reference
• Locks are on a session level
  • A session can only deal with locks for itself.
  • Locks are automatically released when a session ends.
• Locks are at a table level
• LOCK TABLES tableName READ|WRITE [, tablename READ|WRITE]
• UNLOCK TABLES
• To lock tables
  • You must have LOCK_TABLES and SELECT privilege for all tables locked
  • You can lock a table or a view
  • All base tables for the view are locked.
  • Permitted but ignored on temporary tables. (The session is the only thing with access to temporary tables)
• READ lock
  • Multiple sessions can obtain a read lock for the same table.
  • No writing is permitted to the table by any session
• WRITE lock
  • Only the session with the lock has access to the table.
  • Other sessions lock requests on the table block.
• If LOCK TABLES blocks, it does so until all locks can be acquired.
• All locks must be obtained on a single call to LOCK TABLES
  • LOCK_TABLES will release all other locks.
  • The session can not access other table not locked.
• It executes the locks as follows
  • Sorts the tables according to some unspecified scheme.
  • Process write locks first
  • Lock the tables one at a time.
  • This process "ensures that table locking is deadlock free"
• INNODB also supports RECORD, Gap and NEXT-Key locks
  • Record lock: Locks an index record
  • Gap lock: The gap between records is locked
  • Next-Key lock: record and gap lock.
  • These are done as statements are executed to implement the various transaction isolation levels we have discussed.
• The INNODB transaction model
  • Reference
  • If autocommit is on, each sql statement is a single transaction
  • If there is no error, the result is committed
  • If there is an error, most statements do a full rollback