Fork me on GitHub
余鸢

MYSQL锁的用法

发表于 | 分类于

表级锁

表级锁可以作为ENGINE = MyISAM的重要工具，但对于ENGINE = InnoDB很少有用。 如果您试图用InnoDB使用表级锁，您应该重新思考如何处理事务。

MySQL允许客户端会话明确地获取表级锁，以便与其他会话进行协作以访问表，或者阻止其他会话在会话需要独占访问期间修改表。 会话只能为自己获取或释放锁。 一个会话无法获取另一个会话的锁或释放由另一个会话持有的锁。

可以使用锁来模拟事务，或者在更新表时获得更多的速度。 这将在本节后面更详细地解释。

命令：LOCK TABLES table_name READ|WRITE;

你只能将锁类型分配给单个表;

示例（READ LOCK）：

1
LOCK TABLES table_name READ;

示例 (WRITE LOCK):

1
LOCK TABLES table_name WRITE;

要看到锁是否被应用，请使用以下命令

1
SHOW OPEN TABLES;

要刷新/删除所有锁，请使用以下命令：

1
UNLOCK TABLES;

例：

1
2
3
LOCK TABLES products WRITE:  
INSERT INTO products(id,product_name) SELECT id,old_product_name FROM old_products;
UNLOCK TABLES;

以上示例任何外部连接都无法将任何数据写入products表，直到解锁表products

例：

1
2
3
LOCK TABLES products READ:  
INSERT INTO products(id,product_name) SELECT id,old_product_name FROM old_products;
UNLOCK TABLES;

以上示例任何外部连接都无法从products表中读取任何数据，直到解锁表products

行级锁

如果表使用InnoDB，MySQL会自动使用行级锁定，以便多个事务可以同时使用同一个表进行读取和写入，而不会使对方等待。

如果两个事务尝试修改同一行，并且都使用行级别锁定，其中一个事务将等待其他事务完成。

对于预期要修改的每行，可以通过使用SELECT ... FOR UPDATE语句获得行级别锁定。

考虑两个连接来详细说明行级锁定

连接1

1
2
START TRANSACTION;
SELECT ledgerAmount FROM accDetails WHERE id = 1 FOR UPDATE;

在连接1中，通过SELECT ... FOR UPDATE语句获取行级锁。

连接2

1
UPDATE accDetails SET ledgerAmount = ledgerAmount + 500 WHERE id=1;

当有人尝试更新连接2中的同一行时，将等待连接1完成事务，否则将根据innodb_lock_wait_timeout设置显示错误消息，默认值为50秒。

1
Error Code: 1205. Lock wait timeout exceeded; try restarting transaction

要查看有关此锁的详细信息，请运行SHOW ENGINE INNODB STATUS

1
2
3
4
5
6
---TRANSACTION 1973004, ACTIVE 7 sec updating
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 4, OS thread handle 0x7f996beac700, query id 30 localhost root update
UPDATE accDetails SET ledgerAmount = ledgerAmount + 500 WHERE id=1
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:

连接2

1
2
UPDATE accDetails SET ledgerAmount = ledgerAmount + 250 WHERE id=2;
1 row(s) affected

但是，更新连接2中的其他一行将被执行而没有任何错误。

连接1

1
2
3
UPDATE accDetails SET ledgerAmount = ledgerAmount + 750 WHERE id=1;
COMMIT;
1 row(s) affected

现在行锁被释放，因为在连接1中提交事务。
连接2

1
2
UPDATE accDetails SET ledgerAmount = ledgerAmount + 500 WHERE id=1;
1 row(s) affected

通过完成事务，连接1释放行锁之后，更新在连接2中执行，没有任何错误。

备注

锁定用于解决并发问题。仅在运行事务时才需要锁定，首先从数据库读取值，然后将该值写入数据库。 自包含的插入，更新或删除操作不需要锁定。

有两种可用的锁

READ LOCK - 用户只读表时。

WRITE LOCK - 当用户正在读取和写入表格时。

当用户在表上持有WRITE LOCK时，没有其他用户可以读取或写入该表。 当用户在表上持有READ LOCK时，其他用户也可以读取或保持READ LOCK，但是没有用户可以在该表上写入或保持WRITE LOCK。

如果默认存储引擎是InnoDB，MySQL会自动使用行级锁定，以便多个事务可以同时使用同一个表进行读取和写入，而不会使对方等待。

对于InnoDB以外的所有存储引擎，MySQL使用表锁定。