0

Should i use transaction for single Select, Insert, Update, Delete statements in SQL Server?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nik
  • 61
  • 7
  • 1
    Depends on transaction settings... – jarlh May 12 '15 at 09:42
  • 1
    Depends on your use case. – Uwe Allner May 12 '15 at 09:44
  • 1
    do you only want yes or no for an answer? – MAC May 12 '15 at 09:48
  • Settings are default. I want an explanation – Nik May 12 '15 at 09:50
  • I assume, lets say we have mutiple users calling the same SP, that has a single Select statement. Considering concurreny..when the first users calls the SP, will the call from second user be on hold. I mean, if i use Transaction for a single select statements. Are there any kind of lock acquired on the calls? – Nik May 12 '15 at 09:56
  • 1
    See http://dba.stackexchange.com/q/43254/6137 – PHeiberg May 13 '15 at 11:33
  • possible duplicate of [What does a transaction around a single statement do?](http://stackoverflow.com/questions/1171749/what-does-a-transaction-around-a-single-statement-do) – PHeiberg May 13 '15 at 11:34

1 Answers1

1

Could be useful in case you need to avoid phantoms, dirty reads or other issues like those. It could also depends on the framework you're using to perform such operations, but in a very simple scenario (one single workflow) transactions are not needed

Simone
  • 1,828
  • 1
  • 13
  • 20
  • Ok. Let me take anothor scenario. – Nik May 12 '15 at 09:53
  • I have a stored procedure written. It has three select statements. Getting data from three seperate tables. First table is primary to the second table. And Second table is primary to the third table. Should i keep these three select statements in a transaction within a SP. If yes, why? If no, why? – Nik May 12 '15 at 09:54
  • 1
    if you're sure no one is performing any INSERT/UPDATE/DELETE on those tables in the meantime the answer is no, you don't need any transaction, since reading a table doesn't alter its records and you'll always have repeatable reads. In case someone should change the records, then you need transactions to avoid phantoms, dirty reads and company issues – Simone May 12 '15 at 10:02
  • Ok. What if the INSERT/Update/Delete statements are in transaction. Will the select queries wait to get the data, after the DML statements have committed. – Nik May 12 '15 at 10:29
  • Yes and no: it depends on the isolation level and/or the lock type on the table. The lower isolation level usually let you have concurrent ops, while the highest used with a table lock guarantee no clash is occurring making the distinct ops serialized. – Simone May 12 '15 at 10:38