Get UnCommitted Data in MySQL

问题:

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

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

回答2:

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

Reference

回答3:

SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. Otherwise, this isolation level works like READ COMMITTED.

SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

autocommit

Command-Line Format --autocommit[=#]
System Variable Name    autocommit
Variable Scope  Global, Session
Dynamic Variable    Yes
Permitted Values    Type    boolean
Default ON

The autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0, you must use COMMIT to accept a transaction or ROLLBACK to cancel it. If autocommit is 0 and you change it to 1, MySQL performs an automatic COMMIT of any open transaction. Another way to begin a transaction is to use a START TRANSACTION or BEGIN statement. See Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.

By default, client connections begin with autocommit set to 1. To cause clients to begin with a default of 0, set the global autocommit value by starting the server with the --autocommit=0 option. To set the variable using an option file, include these lines:

[mysqld]
autocommit=0

See This also

回答4:

Within code igniter, you can use the following command before any request:

$this->db->simple_query("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");

You can get more information about MySQL isolation level in the documentation. This requires innoDB tables.

For more information about simple_query(), according to codeigniter documentation, it is used when a query returns no result.

回答5:

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.