Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Table Locking

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Table Locking

      MySQL database provides a multi-user environment, that allows multiple clients to access the database at the same time. To run this environment smoothly, MySQL introduced the concept of locks.
      A client in a session can lock a certain table they are working on, in order to prevent other clients from using the same table. This process will avoid any data losses that might occur when multiple users work on the same table simultaneously.
      A client can lock a table and unlock it whenever needed. However, if a table is already locked by a client session, it cannot be accessed by other client sessions until it is released.

      Locking Tables in MySQL

      You can restrict the access to records of the tables in MYSQL by locking them. These locks are used to keep other sessions away from modifying the tables in the current session.
      MySQL sessions can acquire or release locks on the table only for itself. To lock a table using the MySQL LOCK TABLES Statement you need have the TABLE LOCK and SELECT privileges.
      These locks are used to solve the concurrency problems. There are two kinds of MYSQL table locks
      • READ LOCK − If you apply this lock on a table the write operations on it are restricted. i.e., only the sessions that holds the lock can write into this table.
      • WRITE LOCK − This lock allows restricts the sessions (that does not possess the lock) from performing the read and write operations on a table.

      Syntax

      Following is the syntax of the MySQL LOCK TABLES Statement 
      LOCK TABLES table_name [READ | WRITE];

      Unlocking Tables in MySQL

      Once the client session is done using/accessing a MySQL table, they must unlock the table for other client sessions to use it. To do so, you can use the MySQL UNLOCK TABLE statement. This will release the table until other sessions lock it again.

      Syntax

      Following is the syntax of the MySQL UNLOCK TABLES Statement 
      UNLOCK TABLES;

      Example

      Let us start with creating a table named CUSTOMERS that contains the details as shown below 
      CREATE TABLE CUSTOMERS (
         ID INT AUTO_INCREMENT,
         NAME VARCHAR(20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),
         PRIMARY KEY (ID)
      );
      Now, let's insert 2 records into the above created table using the INSERT statement as 
      INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
        (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
        (2, 'Khilan', 25, 'Delhi', 1500.00 );
      Create another table named BUYERS using the following query 
      CREATE TABLE BUYERS (
         B_ID INT AUTO_INCREMENT,
         B_NAME VARCHAR(20) NOT NULL,
         B_AGE INT NOT NULL,
         B_ADDRESS CHAR (25),
         B_SALARY DECIMAL (18, 2),
         PRIMARY KEY (B_ID)
      );
      Following queries inserts records into the BUYERS table using the INSERT INTO SELECT statement. Here, we are trying to insert records from the CUSTOMERS table to BUYERS table.
      Locking and Unlocking:
      Here before the transfer, we are acquiring the write lock on the BUYERS table to which we are inserting records and acquiring read lock on the CUSTOMERS table from which we are inserting records. Finally, after the transfer we are releasing the records.
      LOCK TABLES CUSTOMERS READ, BUYERS WRITE;
      
      INSERT INTO BUYERS (B_ID, B_NAME, B_AGE, B_ADDRESS, B_SALARY)
         SELECT
            ID, NAME, AGE, ADDRESS, SALARY
         FROM
            CUSTOMERS
         WHERE
            ID = 1 AND NAME = 'Ramesh';
      INSERT INTO BUYERS (B_ID, B_NAME, B_AGE, B_ADDRESS, B_SALARY)
         SELECT
            ID, NAME, AGE, ADDRESS, SALARY
         FROM
            CUSTOMERS
         WHERE
            ID = 2 AND NAME = 'Khilan';
      	  
      UNLOCK TABLES;

      Verification

      We can verify the contents of the BUYERS table using the below query 
      SELECT * FROM BUYERS;
      As we can see in the BUYERS table, the records has been transferred.
      B_ID
      B_NAME
      B_AGE
      B_ADDRESS
      B_SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00

      Table Locking Using a Client Program

      Besides locking a table in a MySQL database with a MySQL query, we can also use a client program to perform the LOCK TABLES operation.

      Syntax

      Following are the syntaxes to Lock a table in MySQL in various programming languages
      PHPNodeJSJavaPython
      To lock the table in MySQL database through a PHP program, we need to execute the Lock Tables statement using the mysqli function query() as 
      $sql="LOCK TABLES table_name [READ | WRITE]";
      $mysqli->query($sql);

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      // printf('Connected successfully.');
      
      // Here we are locking two table;
      $sql = "LOCK TABLES tut_tbl READ, clone_table WRITE";
      if ($mysqli->query($sql)) {
          printf("Table locked successfully!.");
      }
      if ($mysqli->errno) {
          printf("Table could not be locked!.", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      Table locked successfully!.