在一个原子操作中从InnoDB表中进行选择和截断的最佳方法是什么? [英] What is the best way to SELECT from and TRUNCATE an InnoDB table in one atomic operation?

问题描述

我想使用SELECT从InnoDB表读取，然后在一个操作中使用TRUNCATE截断它，以便其他查询必须等待TRUNCATE操作完成才能修改表.最好的方法是什么?据我了解，表锁不适用于TRUNCATE.但是从我的测试来看，事务 do 与TRUNCATE一起使用.但是，只有在隔离级别为SERIALIZABLE的情况下，才能保证事务不重叠.

I want to read from an InnoDB table using SELECT and then truncate it using TRUNCATE in one operation such that other queries have to wait for the TRUNCATE operation to complete before they can modify the table. What is the best way to do that? From what I understand, table locks don't work with TRUNCATE. But from my tests, transactions do work with TRUNCATE. However transactions are only guaranteed not to overlap if the isolation level is SERIALIZABLE.

我正在使用PHP和MySQLi.默认的隔离级别为REPEATABLE READ，因此我将其更改为SERIALIZABLE，以防止并发查询在事务完成之前修改表:

I am using PHP and MySQLi. The default isolation level is REPEATABLE READ so I am changing it to SERIALIZABLE to prevent concurrent queries from modifying the table before the transaction completes:

$mysqli->query("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");

$mysqli->begin_transaction();

// Select statements reading from `my_table`

$mysqli->query("TRUNCATE `my_table`");

$mysqli->commit();

这似乎很好.我只是想知道是否有更好的方法.

This seems to be working just fine. I'm just wondering if there is a better way.

推荐答案

GET_LOCK()和 RELEASE_LOCK()与事务正交，等等.(它们很少见使用，但您的情况可能需要它们.)

GET_LOCK() and RELEASE_LOCK() are orthogonal to transactions, etc. (They are extremely rarely used, but your case may need them.)

这篇关于在一个原子操作中从InnoDB表中进行选择和截断的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！