在 MySQL 中获取未提交的数据

2022-01-03 00:00:00 php mysql codeigniter codeigniter-3 sql-server-2012

在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:

您可以在运行语句之前在查询中设置隔离级别.下面是简单的php mysqli代码你使用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();

相关文章