Get UnCommitted Data in MySQL

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 READGO

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

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

Reference

Only the InnoDB storage engine fully supports transactions. It also implements Oracle/PostgreSQL-style MVCC that prevents implicit row locks from blocking reads. To get Read-Uncommitted in InnoDB, issue a SET TRANSACTION LEVEL READ UNCOMMITTED before issuing the query.

The syntax for doing so in PHP would look something like this:

$dbh->exec('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED');$dbh->beginTransaction();

This sets the isolation level until the next COMMIT or ROLLBACK. To make the level change persist for the duration of the session, use

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

instead.

As for overriding a non-sharing read lock on a table or row, I'm not sure that's possible, nor can I think of a situation where it would be desirable. Non-sharing locks are usually non-sharing for a reason.