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 ;
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.