Post reply

Serializable Isolation Level

  • November 11, 2009 at 5:03 am

    #142983

    Dear All

    I have some confusion about Serializable Isolation level.

    If I set Serializable Isolation Level and have multiple sql stmt like 2 update 1 insert and 3 select on diffrent - 2 tables , then what will happen

    whether locks will held on all tables untill completion of transaction and other transactions will not be able to do any transaction on those table. ?.

    Please guid me

    Thanks

    Ghanshyam

  • November 11, 2009 at 5:52 am

    #1077736

    Using transactions, you will get locks no matter what isolation level you are using (you will also get locks with an individual statement even if it's not part of a transaction), but the different isolation levels will cause different amounts of locking.

    Serializable is the most restrictive isolation level, and will cause the most locks, so only use it if you really need to.

    That isolation level will place range locks on any data you insert, update or select. A very simplistic example, if your transaction inserts or updates rows with keys "A" and "F", no other user will be able to touch A, B, C, D, E or F until your transaction is commited or rolled back.

    That is also the case for your selects, if you select rows with the keys between "H" and "L", nobody will be able to update or insert any rows in that whole range while your transaction is active.

    Note, there are ways around this using UNCOMMITTED or SNAPSHOT isolation levels, but unless you really need to, don't use SERIALIZABLE.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply