在MySQL中获取未提交的数据 [英] Get UnCommitted Data in MySQL

问题描述

在SQL Server中，我们可以在SQL Queries下面编写，以获取数据库中未提交的数据.这意味着仍在事务中的数据和事务未完成.

In SQL Server we can write below SQL Queries to get Un-Committed data in database . This means the data that is still under transaction and Transaction is not complete.

SQL Server查询

Select * from TableName With(NoLock);

即使表被锁定，MySQL数据库中是否也有任何等效项来获取数据?我正在PHP CodeIgnitor中尝试此操作

推荐答案

找到标题为" MySQL NOLOCK语法"的文章

http://itecsoftware.com/with-nolock-table-hint-equivalent-for -mysql

SQL Server WITH(NOLOCK)看起来像这样:

SQL Server WITH (NOLOCK) looks like this:

SELECT * FROM TABLE_NAME WITH (nolock)

要在MySQL中实现同样的目的，我们使用SET SESSION命令更改会话隔离模式.

To achieve the same with MySQL, we change the session isolation mode using the SET SESSION command.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
 SELECT * FROM TABLE_NAME ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

您也可以通过以下方式实现同​​样的目标:

You can achive same by below also:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
COMMIT ;

此语句的工作方式类似于WITH(NOLOCK)，即READ UNCOMMITTED数据.我们还可以全局设置所有连接的隔离级别:

This statement will work similar to WITH (NOLOCK) i.e READ UNCOMMITTED data. We can also set the isolation level for all connections globally:

 SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

另外，MySQL服务器中还存在两个与隔离相关的系统变量:

In addition, two system variables related to isolation also level exist in MySQL server:

SELECT @@global.tx_isolation; (global isolation level)
SELECT @@tx_isolation; (session isolation level)

或在事务中设置隔离级别:

Or set the isolation level inside a transaction:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

在代码点火器中，您可以使用前两个解决方案包装查询，也可以使用全局选项.

In code igniter you can wrap your query with first two solution or you can use global option.

供参考，您可以使用以下代码:

for your reference you can use below code:

$this->db->query("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");
$this->db->trans_start();

// your code

$this->db->trans_complete();

更新1:

您可以在运行语句之前在查询中设置隔离级别.下面是使用isolation level read uncommited

You can just set the isolation level in a query before you run your statements. Below is the simple php mysqli code tu use isolation level read uncommited

//db connection
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');

//set isolation level
$mysqli->query("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");

//your Select Query
$results = $mysqli->query("SELECT * FROM tablename");


while($row = $results->fetch_assoc()) {
    //some statements
}

// Frees the memory associated with a result
$results->free();
$mysqli->query("COMMIT");
// close connection
$mysqli->close();

这篇关于在MySQL中获取未提交的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！