sql transaction isolation level

Posted on 2011-05-07
Last Modified: 2012-05-11
I am inserting a record in a table ,
Updating another related table if the insertion was successful
Other wise roll back the transaction

What is the best isolation level to use?  I want to have the best performance.

Concerning restoring my database If I did full backup will I loose anything.

Concerning restoring my database if i did partial backup what i should do so as not to loose anything
Question by:sohairzaki2005
    LVL 24

    Accepted Solution

    I think that the normal isolation level that is Read Committed is just fine for what you are doing. Just be aware of how long the transaction is held.  You could do Read Committed Snapshot, but that requires some configuration and it would not make your insert, update, stuff more efficient, but it would not necessarily block others just wanting to read from those tables.

    As far as the backup and restore of a database.  If you backup a database full, and then restore it, the only thing you can lose is anything that happened in between the full backup and the restore.  If you backup the database and take log backups, then you can get up to the point that the database is at before you restore.

    For the restoring the partial backup, I am assuming that you mean a Differential or you could mean that you backed up a file or filegroup, and you stand to lose the data that is committed after the backup and before the restore.

    To not lose anything I would say that you would take a full backup and then log backups and when you get to the point of restore, you can restore up to a certain point by backing up the last log backup and then restoring the full and then the logs up to the point in time.

    Hope that was not to jumbled.
    LVL 11

    Expert Comment

    You just leave the isolation level on the default (Read Committed). This will prevent other queries from reading your new data (in the first and second table inserts and updates)  until your whole transaction is committed. It will also use minimal locks on both your updates and other queries so will be safe and perform well.

    You will not lose data if you restore because transaction must be committed for them to be included in the backup file. Partial backups are the same, anything committed to the transaction log will make it into partial backups.
    LVL 9

    Expert Comment

    Isolation level default Read Committed is fine for your example.
    But you need to use

          //YOUR STATEMENTS insert and updates

    Author Comment

    Thanks to you all

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now