Get UnCommitted Data in MySQL

Question

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 Query

Select * from TableName With(NoLock);

Is there any equivalence in MySQL database to get data even if table is locked ? I am trying this in PHP CodeIgnitor

Answer 1

Found an article with title "MySQL NOLOCK syntax"

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

SQL Server WITH (NOLOCK) looks like this:

SELECT * FROM TABLE_NAME WITH (nolock)

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 ;

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 ;

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();

Update 1:

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();