MySQL 锁定表
锁定是一种与表相关联的机制,用于限制对表中数据的未授权访问。
MySQL允许客户端会话明确获取表锁,以与其他会话协作以访问表数据。 MySQL还允许表锁定,以防止在特定时间段内未经授权将其修改为同一表。
MySQL中的会话只能为其自身获取或释放表上的锁。因此,一个会话无法获取或释放其他会话的表锁。需要注意的是,我们必须具有表锁定和SELECT特权才能进行表锁定。
MySQL中的表锁定主要
用于解决并发问题。将在运行事务时使用它,即,首先从表(数据库)中读取值,然后将其写入表(数据库)中。
MySQL 向表提供
两种类型的锁:
读取锁: 此锁定只允许用户从表中读取数据。
写锁定: 此锁定只允许用户读取和写入表。
请注意,MySQL中使用的默认存储引擎是InnoDB。 InnoDB存储引擎不需要手动锁定表,因为MySQL自动为InnoDB表使用行级锁定。因此,我们可以在同一个表上同时执行多个事务以读取和写入操作,而无需彼此等待。所有其他存储引擎都使用MySQL中的表锁定。
在理解表锁定概念之前,首先,我们将使用以下语句创建一个名为"
info_table "的新表:
CREATE TABLE info_table (
Id int NOT null AUTO_INCREMENT,
Name VARCHAR(50) NOT null,
Message VARCHAR(80) NOT null,
PRIMARY KEY (Id)
);
MySQL LOCK TABLES语句
以下是允许我们显式获取表锁的语法:
LOCK TABLES table_name [READ | WRITE];
在上述语法中,我们已经指定了
表名,在
LOCK TABLES 关键字之后,我们希望在该表名上获得锁定。我们可以指定
锁定类型,即READ或WRITE。
我们还可以通过使用一系列用逗号分隔的表名和锁定类型来锁定MySQL中的多个表。请参见以下语法:
LOCK TABLES tab_name1 [READ | WRITE],
tab_name2 [READ | WRITE],...... ;
MySQL UNLOCK TABLES语句
以下是允许我们
释放MySQL中表的锁的语法:
锁类型
让我们详细了解锁类型。
读取锁
以下是READ锁定的功能:
同时,MySQL允许多个会话获取表的READ锁。其他所有会话都可以在不获取锁的情况下读取表。
如果会话在表上拥有READ锁,则他们无法在该表上执行写操作。这是因为READ锁只能从表中读取数据。所有其他不具有READ锁定的会话,如果不释放READ锁定,则无法将数据写入表中。写入操作进入等待状态,直到我们还没有释放READ锁。
当会话正常或异常终止时,MySQL隐式释放对该表的所有类型的锁。此功能也与WRITE锁定有关。
让我们举个例子来看一下在给定情况下READ锁在MySQL中如何工作。我们将首先连接到数据库,并使用
CONNECTION_ID()函数在第一个会话中提供当前连接ID,如下所示:
mysql> SELECT CONNECTION_ID();
请参见以下输出:
接下来,我们将插入一些使用以下语句将行插入
info_table :
mysql> INSERT INTO info_table (name, message)
VALUES('Peter', 'Hi'),
('Joseph', 'Hello'),
('Mark', 'Welcome');
现在,使用以下语句将数据验证到表中:
mysql> SELECT * FROM info_table;
我们应该看到如下输出:
现在,我们将执行LOCK TABLE语句以获取对该表的锁定:
mysql> LOCK TABLE info_table READ;
在那之后,我们将尝试如下将新记录插入到info_table中:
mysql> INSERT INTO info_table (name, message)
VALUES ('Suzi', 'Hi');
我们将在MySQL发出以下消息时得到以下输出:
"表'info_table'被READ锁锁定,无法更新"。。
因此,我们可以看到,一旦对表获取了READ锁,就无法将数据写入
现在,我们将检查其他会话中的READ锁定的工作方式。首先,我们将连接到数据库并查看连接ID:
下一步,我们将从info_table查询返回以下输出的数据:
然后,将一些行插入到该表中,如下所示:
mysql> INSERT INTO info_table (name, message)
VALUES ('Stephen', 'Hello');
我们应该看到如下输出:
在上面输出,我们可以看到第二个会话的插入操作处于
等待状态。这是由于READ锁定,该锁定已由第一个会话在表上获取,但尚未释放。
我们可以使用
SHOW PROCESSLIST 查看有关它们的详细信息。第一届会议上的"强>"声明。看到下面的输出:
最后,我们需要使用释放锁在第一个会话中使用
UNLOCK TABLES 语句。现在,我们可以在第二个会话中执行INSERT操作。
写锁
以下是WRITE锁的功能:
这是持有表锁并可以从表中读取和写入数据的会话。
这是唯一通过持有锁访问表的会话。在释放WRITE锁定之前,所有其他会话都无法访问表的数据。
让我们举个例子,看看在给定的情况下WRITE锁在MySQL中如何工作。在第一个会话中,我们将使用以下语句获取WRITE锁定:
mysql> LOCK TABLE info_table WRITE;
然后,我们将新记录插入到info_table中,如下所示:
mysql> INSERT INTO info_table (name, message)
VALUES ('Stephen', 'How R U');
以上陈述起作用。现在,我们可以使用SELECT语句验证输出:
同样,我们将尝试从第二个会话访问(读/写)表:
INSERT INTO info_table (name, message)
VALUES ('George', 'Welcome');
SELECT * FROM info_table;
我们可以看到这些操作已进入等待状态。使用SHOW PROCESSLIST语句查看有关它们的详细信息:
最后,我们将从第一个会话释放锁。现在,我们可以执行挂起的操作。
读锁定与写锁定
读取锁类似于" 共享"锁,因为多个线程可以同时获取它。
写锁是" 专有"锁,因为另一个线程无法读取它。
我们无法同时在表上提供读写锁。
读取锁定的优先级比写入锁定的优先级低,这可以确保尽快进行更新。