[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

sql transaction isolation level

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
0
sohairzaki2005
Asked:
sohairzaki2005
1 Solution
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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.
0
 
brutaldevCommented:
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.
0
 
sarabhaiCommented:
Isolation level default Read Committed is fine for your example.
But you need to use

BEGIN TRAN
      //YOUR STATEMENTS insert and updates
COMMIT TRAN
0
 
sohairzaki2005Author Commented:
Thanks to you all
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now