MySQL - 表鎖定

要在 MySQL 資料庫中透過 Node.js 程式鎖定表，我們需要使用 mysql2 庫的 query() 函式執行 Lock 語句，如下所示：

sql = "LOCK TABLES table_name [READ | WRITE]";
con.query(sql);

要在 MySQL 資料庫中透過 Java 程式鎖定表，我們需要使用 JDBC 函式 executeUpdate() 執行 Lock 語句，如下所示：

String sql="LOCK TABLES table_name [READ | WRITE]";
statement.executeUpdate(sql);

要透過 Python 程式鎖定 MySQL 資料庫中的表，我們需要使用 MySQL **Connector/Python** 的 **execute()** 函式執行 **Lock** 語句，如下所示：

sql="LOCK TABLES table_name [READ | WRITE]";
cursorObj.execute(sql);

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE SalesDetails (ID INT, ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchDate date, DeliveryTime time, Price INT, Location VARCHAR(255), CustomerAge INT, CustomrtPhone BIGINT, DispatchAddress VARCHAR(255), Email VARCHAR(50));"
  con.query(sql);

  sql = "insert into SalesDetails values(1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad', 25, '9000012345', 'Hyderabad - Madhapur', 'pujasharma@gmail.com');"
  con.query(sql);
  sql = "insert into SalesDetails values(2, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai', 30, '90000123654', 'Chennai- TNagar', 'vanajarani@gmail.com');"
  con.query(sql);

  sql = "CREATE TABLE CustContactDetails (ID INT,Name VARCHAR(255), Age INT,Phone BIGINT, Address VARCHAR(255), Email VARCHAR(50));"
  con.query(sql);

  sql = "LOCK TABLES SalesDetails READ, CustContactDetails WRITE;"
  con.query(sql);

  sql = "INSERT INTO CustContactDetails (ID, Name, Age, Phone, Address, Email) SELECT ID, CustomerName, CustomerAge, CustomrtPhone, DispatchAddress, Email FROM SalesDetails  WHERE  ID = 1 AND CustomerName = 'Raja';"
  con.query(sql);

  sql = "INSERT INTO CustContactDetails (ID, Name, Age, Phone, Address, Email) SELECT ID, CustomerName, CustomerAge, CustomrtPhone, DispatchAddress, Email FROM  SalesDetails WHERE ID = 2 AND CustomerName = 'Vanaja';"
  con.query(sql);

  sql = "UNLOCK TABLES;"
  con.query(sql);

  sql = "SELECT * FROM CustContactDetails;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

輸出

生成的輸出如下所示：

Connected!
--------------------------
[
  {
    ID: 1,
    Name: 'Raja',
    Age: 25,
    Phone: 9000012345,
    Address: 'Hyderabad - Madhapur',
    Email: 'pujasharma@gmail.com'
  },
  {
    ID: 2,
    Name: 'Vanaja',
    Age: 30,
    Phone: 90000123654,
    Address: 'Chennai- TNagar',
    Email: 'vanajarani@gmail.com'
  }
]

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TableLock {
    public static void main(String[] args) {
        String url = "jdbc:mysql://:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Lock table....
            String sql = "LOCK TABLES tutorials_tbl READ, clone_tbl WRITE";
            statement.executeUpdate(sql);
            System.out.println("Table Locked successfully...!");
            
            connection.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

輸出

獲得的輸出如下所示：

Connected successfully...!
Table Locked successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
table_name = 'tutorials_tbl'
#Creating a cursor object 
cursorObj = connection.cursor()
lock_table_query = f"LOCK TABLES {table_name} WRITE"
cursorObj.execute(lock_table_query)
print(f"Table '{table_name}' is locked successfully.")
cursorObj.close()
connection.close()

輸出

以下是上述程式碼的輸出：

Table 'tutorials_tbl' is locked successfully.