0

Is a bad practice to create a transaction always?

I mean is a good practice to create a transaction only for one simple select.

how much is the cost of creating a transaction when is not really necessary?

Even if you are using an isolation level read_uncomitted. Is a bad practice? because it shouldn't have problems with locking.

Update: Better answer here: https://dba.stackexchange.com/questions/43254/is-a-bad-practice-to-create-a-transaction-always

Community
  • 1
  • 1
elranu
  • 2,292
  • 6
  • 31
  • 55
  • This question would be better suited for dba.stackexchange.com – Kermit May 28 '13 at 15:18
  • use an explicit transaction when you NEED a transaction. – Mitch Wheat May 28 '13 at 15:19
  • I was unsure what you meant by the following phrase `create a transaction only for a simple select or many.` so have edited the question to (hopefully) make it clearer. If that isn't what you intended please clarify it yourself. – Martin Smith May 28 '13 at 15:21
  • @MartinSmith is no a duplication. That question talks about SP and this it does not talk about how much is the cost of an unecesary transaction. I can change the main question. – elranu May 28 '13 at 15:22

1 Answers1

4

Transactions are resource intensive and they also log meta data while doing so. I would recommend staying away from using Transactions on simple SELECT Statements. I would recommend using Transactions on complicated Stored Procedures. You must remember that you run the risk on locking resources if you are using transactions all the time.

Have a quick read here for further explainations - SQL Recommended Best Practices for Transactions

SpaceApple
  • 1,309
  • 1
  • 24
  • 46
  • We are using a DAL that always open a transaction and use read_uncomitted isloation level. With that isolation level we should not have problems with locking. So, its still a bad practice? – elranu May 28 '13 at 15:34
  • 1
    You should never have a transaction unless you are changing data in the database. Read uncommitted isolation level is also a very poor practice and results in data being returned that may be rolled back and thus not really ever in the database. – HLGEM May 28 '13 at 15:45