提升查詢效率與避免LOCK發生

Sql查詢時 會因之前的操作而做lock
因此會有些delay的等待
加了WITH (nolock)就不會理會transaction lock
而直接回覆查詢資料，因而提升回覆速度

• MSSQL
  

  SELECT * FROM TABLE WITH (nolock)

  但會有dirty read，所以較不重要資料才可以這麼用

先在DB打開Snapshot功能，再用command or connection string 控制isolation level。

PHP的mssql server driver 不支援 connection string控制isolation level，因此只能用sql command 或者在DB打開RCSI (READ COMMITTED SNAPSHOT isolation)，全部查詢就會在此isolation level

• MySQL
  
  1. 利set session
     SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
  2. inside a transaction
     SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
     
  3. 設定global variable
     SELECT @@global.tx_isolation; (global isolation level)
SELECT @@tx_isolation; (session isolation level)
     

MySQL uses table-level locking for ISAM, MyISAM, and MEMORY (HEAP) tables, page-level locking for BDB tables, and row-level locking for InnoDB tables.

http://dev.mysql.com/doc/refman/4.1/en/internal-locking.html

References

• WITH (NOLOCK) table hint equivalent for MySQL
• 提升查詢效率與避免LOCK發生