MySQL - SPLessons

MySQL Locking

Chapter 20

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

MySQL Locking

MySQL Locking

shape Description

When different number of users access the same information at the same point of time, locking restrict them for making concurrent alterations to the data. Locks are handled within the Microsoft SQL Server Compact Database Engine. MySQL Locking is automatically captured and discharged based on the operations performed by the client.

If MySQL locking is not performed, then different number of clients can update the same record at the same point of time, the record within the Databases can become logically incorrect. In that case, queries are executed against that record will produce unexpected results.

MySQL Locking issues can be cleared in following two ways

Internal locking

shape Description

Internal Locking can be performed inside the MySQL server for executing the conflict in table substance by various number of threads. This locking is known as Internal Locking. And internal locking is done inside the server end only.

There are two levels of locking:

Row level locking

MySQL utilizes row level locking for InnoDB storage engine in tables to hold simultaneous compose ways for various sessions, making them helpful for various number of users and OLTP operations.

When doing different simultaneous compose applications on a single InnoDB table, accomplish necessary locks before assigning so as to begin the transaction some update explanation for every gathering of rows to avoid deadlocks. Deadlocks irritates the performance rather than defining a extreme error, because InnoDB consequently recognize deadlock conditions and rolls back one of the influenced transactions.

Advantages of row level locking

The advantages of row level locking are

  • In row level locking we can lock a single row for a long time.
  • Update some of the roll backs.
  • Less lock clash when multiple sessions uses multiple rows.

Disadvantages of row level locking

The disadvantages of row level locking are

  • Required more memory for locking.
  • Locking cannot be used on large part of a table because it required many more locks.
  • Row locking – Which are implemented by Innodb
  • Lock – For getting access with cooperative tables
  • Tables – Which store information in rows and columns
  • Application – Which is an open source grid.

Table level locking

MySQL utilizes table level locking for store engines, and permitting only one table to update at certain point of time. Locking level makes these storage engines more applicable for read and single-user operations.

There is no dead locks in MySQL table level locking. Deadlock interceptions can be handled by requesting all the required lock at once, when starting a query and inserting lock condition on required tables in the same order. And there are two lock tables, read lock table and write lock table.

If write lock is not available then assign read lock table else in the read lock queue put a lock request. If locks are not available then put write lock on the table else in the write lock queue put a lock request.

External locking

shape Description

External locking is utilized as a part of the file system for controlling the conflicts to databases tables on various applications. External locking is accomplished when the server and different programs lock the table documents for planning among themselves.

This type of locking disturb the execution of the server, and all the time the server has to wait for accessing the processes. External locking is not required when read operation is performed on the tables with other programs.

Summary

shape Key Points

  • MySQL Locking is the process of restricting users to access same data.
  • Internal locking is performed from the server end only.
  • External locking is used in file system for controlling the database tables.