面試題精選14-資料庫中如何實現行鎖和表鎖

相遇就是有缘發表於2024-11-26
面試題資料庫

行鎖（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;
  1. 使用 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;
  1. 使用 TABLOCK 提示：在查詢中新增 TABLOCK 提示可以指示 SQL Server 使用表鎖。
INSERT INTO Table1 VALUES (Value1, Value2) OPTION (TABLOCK);
  1. 使用 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表執行查詢時，整個表會被鎖定，直到查詢完成。

相關文章