SQL Server Performance

Serializable Vs Read Committed

Discussion in 'Performance Tuning for DBAs' started by Sri316, Jun 28, 2005.

  1. Sri316 New Member

    Hi SQL Guru's
    What is the difference between Serializable and Read Committed wrt locking? In a serializable locking, if a data is locked - the lock wont be released till the transaction ends. Right? is it the same case with Read Committed. If so what benefits has Read committed got over Serializable. Please lemme now

  2. satya Moderator

    By default SQL Server runs under the READ COMMITTED isolation level, and range locks occur only when we go for a higher isolation level called SERIALIZABLE.

    Using SERIALIZABLE was to enforce correctness of data. To make sure the model always works with regards to transactional correctness the process was made to only use SERIALIZABLE transaction isolation.

    READ COMMITTED - Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.

    Linkshttp://www.sql-server-performance.com/reducing_locks_hints.asp &http://www.sql-server-performance.com/at_sql_locking.asp for more information on locking

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. Sri316 New Member

    Hi satya
    Thanks for the prompt response. What do you mean when you said Range Shared locks? Please let me know.

  4. satya Moderator

  5. gurucb New Member

    Simple put,

    In serializable you will not be able to insert a new row in the same range (if index is present) or no insert on whole table(if indiex is not put) when some other spid is reading the data.

Share This Page