面试题精选-数据库中如何实现行锁和表锁

行锁（Row Lock）

SQLSERVER

行锁是在数据行层面上实施的锁定。当你对特定的行执行操作时，SQL Server 通常会自动使用行锁来确保数据的一致性和隔离性。

1. 使用事务并指定隔离级别：在事务中使用适当的隔离级别可以使 SQL Server 在需要时使用行锁。

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 或其他适合的隔离级别

-- 执行需要行锁的 SQL 语句
UPDATE Table1
SET Column1 = Value1
WHERE ID = SomeValue;

COMMIT TRANSACTION;

2. 使用 ROWLOCK 提示：在查询中添加 ROWLOCK 提示可以指示 SQL Server 尝试使用行锁。

UPDATE Table1
SET Column1 = Value1
WHERE ID = SomeValue
OPTION (ROWLOCK);

MySQL

行锁是在InnoDB存储引擎中实现的，它只锁定表中的一行数据。行锁是自动加上的，当你在事务中执行以下操作时：

1. SELECT ... FOR UPDATE: 当你希望更新所选择的行时，可以使用这个语句加行锁。
2. SELECT ... LOCK IN SHARE MODE: 当你希望读取某行并且确保其他事务不会更改这些行时，可以使用这个语句。

以下是示例：

START TRANSACTION; -- 开始事务

SELECT * FROM table_name WHERE id = 1 FOR UPDATE; -- 对id为1的行加行锁

-- 执行一些更新操作
UPDATE table_name SET column_name = 'value' WHERE id = 1;

COMMIT; -- 提交事务，释放锁

表锁（Table Lock）

SQLSERVER

表锁是在整个表层面上实施的锁定。当你需要对整个表进行操作时，表锁会非常有用。

1. 使用事务并指定表锁：

BEGIN TRANSACTION;

-- 执行需要表锁的 SQL 语句
SELECT * FROM Table1 WITH (TABLOCK);

COMMIT TRANSACTION;

2. 使用 TABLOCK 提示：在查询中添加 TABLOCK 提示可以指示 SQL Server 使用表锁。

INSERT INTO Table1 VALUES (Value1, Value2) OPTION (TABLOCK);

3. 使用 HOLDLOCK 提示：如果你想要在整个事务期间持有表锁，可以使用 HOLDLOCK** **

SELECT * FROM Table1 WITH (HOLDLOCK);

MySQL

表锁可以锁定整个表，使得其他事务不能对该表进行写操作。表锁可以在MyISAM和InnoDB存储引擎中使用。

1. LOCK TABLES: 这个语句可以显式地对一个或多个表加锁。
2. UNLOCK TABLES: 用来释放之前通过LOCK TABLES语句获得的锁。

以下是示例：

LOCK TABLES table_name WRITE; -- 对table_name表加写锁

-- 执行一些操作，比如插入、更新或删除
INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');

UNLOCK TABLES; -- 释放锁

• InnoDB存储引擎：默认情况下，InnoDB会使用行锁，当它检测到事务中涉及到的行时。但是，在某些情况下，InnoDB也会退化到表锁。
• MyISAM存储引擎：默认使用表锁。当你对MyISAM表执行查询时，整个表会被锁定，直到查询完成。