Optimizing Locking Operations The server attempts reparsing up to three times. An error occurs if all attempts fail. Reparsing is automatic, but to the extent that it occurs, diminishes prepared statement and stored program performance. For prepared statements, the  Com_stmt_reprepare  status variable tracks the number of repreparations. MySQL manages contention for table contents using locking: This section discusses internal locking; that is, locking performed within the MySQL server itself to manage contention for table contents by multiple sessions. This type of locking is internal because it is performed entirely by the server and involves no other programs. For locking performed on MySQL files by other programs, see Section 8.11.5, “External Locking” . MySQL uses row-level locking for  InnoDB  tables to support simultaneous write access by multiple sessions, making them suitable for multi-user, highly concurrent, and OLTP applications. To avoid deadlocks when performing multiple concurrent write operations on a single  InnoDB  table, acquire necessary locks at the start of the transaction by issuing a  SELECT ... FOR UPDATE  statement for each group of rows expected to be modified, even if the data change statements come later in the transaction. If transactions modify or lock more than one table, issue the applicable statements in the same order within each transaction. Deadlocks affect performance rather than representing a serious error, because  InnoDB  automatically detects deadlock conditions by default and rolls back one of the affected transactions. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout  setting for transaction rollback when a deadlock occurs. Deadlock detection can be disabled using the  innodb_deadlock_detect  configuration option. •Fewer lock conflicts when different sessions access different rows. •Fewer changes for rollbacks. •Row-Level Locking •Table-Level Locking •Choosing the Type of Locking •Internal locking is performed within the MySQL server itself to manage contention for table contents by multiple threads. This type of locking is internal because it is performed entirely by the server and involves no other programs. See Section 8.11.1, “Internal Locking Methods” . •External locking occurs when the server and other programs lock  MyISAM  table files to coordinate among themselves which program can access the tables at which time. See Section 8.11.5, “External Locking” . 8.11 Optimizing Locking Operations 8.11.1 Internal Locking Methods Row-Level Locking 1822 Advantages of row-level locking: