MySQL

MySQL IntroductionComment MySQLInstall MySQL container with Docker-ComposeMySQL AdminMySQL ALTER TABLEMySQL ArithmeticMySQL BackticksMySQL Backup using mysqldumpMySQL Change PasswordMySQL Character Sets and CollationsMySQL clientMySQL ClusteringMySQL Configuration and tuningMySQL Connecting with UTF-8 Using Various Programming language.MySQL Converting from MyISAM to InnoDBMySQL Create New UserMySQL Creating databasesMySQL Customize PS1MySQL Data TypesMySQL Date and Time OperationsMySQL Dealing with sparse or missing dataMySQL DELETEMySQL Drop TableMySQL Dynamic Un-Pivot Table using Prepared StatementMySQL ENUMMySQL Error 1055: ONLY FULL GROUP BY: something is not in GROUP by clause ...MySQL Error codesMySQL EventsMySQL Extract values from JSON typeMySQL Full-Text searchMySQL Group byMySQL Handling Time ZonesMySQL Indexes and KeysMySQL INSERTMySQL JoinsMySQL JOINS: Join 3 table with the same name of id.MySQL JSONMySQL Limit and OffsetMySQL LOAD DATA INFILEMySQL LOCK TABLEMySQL Log filesMySQL Many-to-many Mapping tableMySQL MyISAM EngineMySQL mysqlimportMySQL NULLMySQL One to ManyMySQL ORDER byMySQL PartitioningMySQL Performance TipsMySQL Performance TuningMySQL Pivot queriesMySQL PREPARE StatementsMySQL Recover from lost root passwordMySQL Regular ExpressionsMySQL ReplicationMySQL Reserved WordsMySQL Security via GRANTsMySQL SELECTMySQL Server InformationMySQL SSL Connection SetupMySQL Stored routines (procedures and functions)MySQL String operationsMySQL Table CreationMySQL Temporary TablesMySQL Time with subsecond precisionMySQL TransactionMySQL TRIGGERSMySQL UNIONMySQL UnionsMySQL UPDATEMySQL Using VariablesMySQL VIEWRecover and reset the default root password for MySQL 5.7+



MySQL LOCK TABLE

From WikiOD

Syntax[edit | edit source]

  • LOCK TABLES table_name [READ | WRITE]; // Lock Table
  • UNLOCK TABLES; // Unlock Tables

Remarks[edit | edit source]

Locking is used to solve concurrency problems.Locking is required only when running a transaction, that first read a value from a database and later write that value in to the database. Locks are never required for self-contained insert, update, or delete operations.

There are two kinds of locks available

READ LOCK - when a user is only reading from a table.

WRITE LOCK - when a user is doing both reading and writing to a table.

When a user holds a WRITE LOCK on a table, no other users can read or write to that table. When a user holds a READ LOCK on a table, other users can also read or hold a READ LOCK, but no user can write or hold a WRITE LOCK on that table.

If default storage engine is InnoDB, MySQL automatically uses row level locking so that multiple transactions can use same table simultaneously for read and write, without making each other wait.

For all storage engines other than InnoDB, MySQL uses table locking.

For more details about table lock See here

Mysql Locks[edit | edit source]

Table locks can be an important tool for ENGINE=MyISAM, but are rarely useful for ENGINE=InnoDB. If you are tempted to use table locks with InnoDB, you should rethink how you are working with transactions.

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

Locks may be used to emulate transactions or to get more speed when updating tables. This is explained in more detail later in this section.

Command:LOCK TABLES table_name READ|WRITE;

you can assign only lock type to a single table;

Example (READ LOCK):

LOCK TABLES table_name READ;

Example (WRITE LOCK):

LOCK TABLES table_name WRITE;

To see lock is applied or not, use following Command

SHOW OPEN TABLES;

To flush/remove all locks, use following command:

UNLOCK TABLES;

EXAMPLE:

LOCK TABLES products WRITE:  
INSERT INTO products(id,product_name) SELECT id,old_product_name FROM old_products;
UNLOCK TABLES;

Above example any external connection cannot write any data to products table until unlocking table product

EXAMPLE:

LOCK TABLES products READ:  
INSERT INTO products(id,product_name) SELECT id,old_product_name FROM old_products;
UNLOCK TABLES;

Above example any external connection cannot read any data from products table until unlocking table product

Row Level Locking[edit | edit source]

If the tables use InnoDB, MySQL automatically uses row level locking so that multiple transactions can use same table simultaneously for read and write, without making each other wait.

If two transactions trying to modify the same row and both uses row level locking, one of the transactions waits for the other to complete.

Row level locking also can be obtained by using SELECT ... FOR UPDATE statement for each rows expected to be modified.

Consider two connections to explain Row level locking in detail

Connection 1

START TRANSACTION;
SELECT ledgerAmount FROM accDetails WHERE id = 1 FOR UPDATE;

In connection 1, row level lock obtained by SELECT ... FOR UPDATE statement.

Connection 2

UPDATE accDetails SET ledgerAmount = ledgerAmount + 500 WHERE id=1;

When some one try to update same row in connection 2, that will wait for connection 1 to finish transaction or error message will be displayed according to the innodb_lock_wait_timeout setting, which defaults to 50 seconds.

Error Code: 1205. Lock wait timeout exceeded; try restarting transaction

To view details about this lock, run SHOW ENGINE INNODB STATUS

***TRANSACTION 1973004, ACTIVE 7 sec updating
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 4, OS thread handle 0x7f996beac700, query id 30 localhost root update
UPDATE accDetails SET ledgerAmount = ledgerAmount + 500 WHERE id=1
******* TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:

Connection 2

UPDATE accDetails SET ledgerAmount = ledgerAmount + 250 WHERE id=2;
1 row(s) affected

But while updating some other row in connection 2 will be executed without any error.

Connection 1

UPDATE accDetails SET ledgerAmount = ledgerAmount + 750 WHERE id=1;
COMMIT;
1 row(s) affected

Now row lock is released, because transaction is commited in Connection 1.

Connection 2

UPDATE accDetails SET ledgerAmount = ledgerAmount + 500 WHERE id=1;
1 row(s) affected

The update is executed without any error in Connection 2 after Connection 1 released row lock by finishing the transaction.

Credit:Stack_Overflow_Documentation