在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屋!