3

I need to insert data into two separate tables in the same database. I'm currently using a separate query/transaction for each insert, but I recently learned that I can combine the two inserts into a single transaction.

What are the differences in combining them into one transaction instead of keeping them as separate transactions?

user823447
  • 147
  • 2
  • 11
Evil Washing Machine
  • 1,293
  • 4
  • 18
  • 43

4 Answers4

10

Well Transaction provides you the facility to rollback your sql statement. For example you have two tables table1 and table2, you want to insert data in table2 only if it inserts in table1 properly, If there is an error occured while inserting data in table1 you will not insert data in table2, in this scenario you will use transaction.

Four Basic Functions of Transactions:

  1. Atomicity: when two or more pieces of information are involved in a transaction either all the peices are commited or none of them are commited.
  2. Consistency: at the end of a transaction, either a new and valid form of data exists or data is returned to its orignal state.
  3. Isolation: During a transaction ( before it is commited or rolled back ) the data must remain in isolated state and not accessible to other transactions.
  4. Durability: After a transaction is comitted the final state of the data is still available even if the server fails or restarted.
Waqar Janjua
  • 6,113
  • 2
  • 26
  • 36
4

If the data are correlated, which means that the data stored by the second sql depend, are linked in some way with the data which are stored by the first query and/or vice-versa, then you should consider to make a transaction. In such this way, you could rollback if one of the queries encounter a problem. You can implement this in a try-catch statement. If the queries are totally unrelated each-other, you can go with two separate SQL statements.

However I think that, as the data are the same for the two tables, it would be better to do a transaction. So, if you'll have to make operations like joins, deletes, etc. in the future, you have a consistent database yet.

A_nto2
  • 1,106
  • 7
  • 16
1

One thing has got nothing to do with the other.

The point of putting them both in the same transaction is both occur or neither.

If your first insert succeeds and the second one fails, where does that leave you is the question you need to ask, and answer.

Personally since I'm doing both inserts in some operation, a single transaction would make sense even if the consequences of the failure of the second one were dealt with elsewhere.

Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39
1

Do you need to perform these 2 inserts atomically (i.e. they must both succeed or fail, but you cannot have one succeed and the other fail)?

  • If yes, then you must enclose them in the same transaction.
  • If no, then you have some latitude in your choice, as described below...

Transactions are ACID and "D" stands for "durable", which in practice means the DBMS must wait until data is physically written to the permanent storage (instead of just storing it in cache and immediately moving on). For performance reasons, you'll want this "durability wait" to be relatively rare, and having one transaction instead of two (one for each statement) is better in this regard. Of course, this would make them atomic, which may or may not be desirable, so you'll have to balance the performance with the desired behavior.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167