Your cart is currently empty!
Category: mysql
-
Khóa học miễn phí MySQL – Table Locking nhận dự án làm có lương
MySQL – Table Locking
Table of content
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 −
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);
To lock the table in MySQL database through a Node.js program, we need to execute the Lock statement using the query() function of the mysql2 library as −
sql = "LOCK TABLES table_name [READ | WRITE]"; con.query(sql);
To lock the table in MySQL database through a Java program, we need to execute the Lock statement using the JDBC function executeUpdate() as −
String sql="LOCK TABLES table_name [READ | WRITE]"; statement.executeUpdate(sql);
To lock the table in MySQL database through a Python program, we need to execute the Lock statement using the execute() function of the MySQL Connector/Python as −
sql="LOCK TABLES table_name [READ | WRITE]"; cursorObj.execute(sql);
Example
Following are the programs −
$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!.
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); }); });Output
The output produced is as follows −
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://localhost: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); } } }Output
The output obtained is as shown below −
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()Output
Following is the output of the above code −
Table ''tutorials_tbl'' is locked successfully.
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
-
Khóa học miễn phí MySQL – Create Database nhận dự án làm có lương
MySQL – Create Database
After establishing connection with MySQL, to manipulate data in it you need to connect to a database. You can connect to an existing database or, create your own.
You would need special privileges to create or to delete a MySQL database. So, if you have access to the root user, you can create any database using the MySQL CREATE DATABASE statement.
MySQL CREATE Database Statement
The CREATE DATABASE statement is a DDL (Data Definition Language) statement used to create a new database in MySQL RDBMS.
If you are creating your database on Linux or Unix, then database names are case-sensitive, even though keywords SQL are case-insensitive. If you are working on Windows then this restriction does not apply.
Syntax
Following is the syntax to create a database in MySQL −
CREATE DATABASE DatabaseName;
Where, the “DatabaseName” is just a placeholder representing the name of the database that we want to create.
Example
Let us create a database TUTORIALS in MySQl using the CREATE DATABASE statement as follows −
CREATE DATABASE TUTORIALS;
Make sure you have the necessary privilege before creating any database.
Verification
Once the database TUTORIALS is created, we can check it in the list of databases using the SHOW statement as shown below −
SHOW DATABASES;
Following are the list of databases present in the server −
| Database |
|---|
| information_schema |
| mysql |
| performance_schema |
| tutorials |
CREATE Database with IF NOT EXISTS clause
If you try to create a database with an existing name an error will be generated. Suppose there is an existing database in MySQL with the name mydb and if we try to create another database with the same name as −
CREATE DATABASE myDatabase
An error will be generated as shown below −
ERROR 1007 (HY000): Can''t create database ''mydb database exists
If you use the IF NOT EXISTS clause along with the CREATE statement as shown below a new database will be created and if a database with the given name, already exists the query will be ignored.
CREATE DATABASE IF NOT EXISTS myDatabase
Create Database Using mysqladmin
You would need special privileges to create or to delete a MySQL database. So assuming you have access to the root user, you can create any database using the mysql mysqladmin binary.
Example
Here is a simple example to create a database named TUTORIALS using mysqladmin −
[root@host]# mysqladmin -u root -p create TUTORIALS Enter password:******
This will create a MySQL database called TUTORIALS.
Creating Database Using a Client Program
Besides creating a database in MySQL RDBMS with a MySQL query, you can also use a client program in programming languages such as Node.js, PHP, Java, and Python to achieve the same result.
Syntax
Following are the syntaxes of this operation in various programming languages −
To create a database in MySQL RDBMS through a PHP program, we need to execute the ”CREATE DATABASE” statement using the mysqli function named query() as shown below −
$sql = "CREATE DATABASE DatabaseName"; $mysqli->query($sql);
To create a database in MySQL RDBMS through a Node.js program, we need to execute the ”CREATE DATABASE” statement using the query() function of the mysql2 library as follows −
sql = "CREATE DATABASE DatabaseName";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});
To create a database in MySQL RDBMS through a Java program, we need to execute the ”CREATE DATABASE” statement using the JDBC function executeUpdate() as follows −
String sql = "CREATE DATABASE DatabaseName"; st.executeUpdate(sql);
To create a database in MySQL RDBMS through a Python program, we need to execute the ”CREATE DATABASE” statement using the execute() function of the MySQL Connector/Python as follows −
sql = "CREATE DATABASE DatabaseName" cursorObj.execute(sql)
Example
Following are the programs −
$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''root@123
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf(''Connected successfully.<br />'');
if ($mysqli->query("CREATE DATABASE TUTORIALS")) {
printf("Database created successfully.<br />");
}
if ($mysqli->errno) {
printf("Could not create database: %s<br />", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as follows −
Connected successfully. Database created successfully.
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("--------------------------");
//Creating a Database
sql = "create database TUTORIALS"
con.query(sql, function(err){
if (err) throw err
console.log("Database created successfully...")
});
});
Output
The output produced is as follows −
Connected! -------------------------- Database created successfully...
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class createDatabase {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/";
String user = "root";
String password = "password";
ResultSet rs;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement();
//System.out.println("Connected successfully...!");
String sql = "CREATE DATABASE TUTORIALS";
st.execute(sql);
System.out.println("Database created successfully...!");
}catch(Exception e) {
e.printStackTrace();
}
}
}
Output
The output obtained is as shown below −
Database created successfully...!
import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
host ="localhost",
user ="root",
password ="password"
)
# creating cursor object
cursorObj = connection.cursor()
# creating the database
cursorObj.execute("CREATE DATABASE MySqlPythonDB")
print("Database Created Successfully")
# disconnecting from server
connection.close()
Output
Following is the output of the above code −
Database Created Successfully
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
