CODEWITHSUNDEEP
transactionsisolation-level
Doflamingo
Doflamingo

Reputation: 217

Which is the best transaction isolation level for insert row?

I need to understand which is the best transaction isolation that I can use to do this 2 things:

  1. Save a row in table A
  2. Save a row in table B

I know that if I use like transaction isolation SERIALIZABLE, it works fine, but I need to know if it is possibile that I can use READ_COMMITED, or REPEATEBLE_READ. I save only two rows in two different table so I'm not doing READ operation, so I think that READ_COMMIT is the best solution? Anyone can help me?

Upvotes: 1

Views: 6141

Answers (1)

John Humphreys
John Humphreys

Reputation: 39224

Is there a requirement to have the new A and B rows be persisted together and only visible together? If you're not reading anything and you are OK with A and B not necessarily showing up together, then you don't even need a transaction here.

This is a good related read: https://www.sqlpassion.at/archive/2014/01/21/myths-and-misconceptions-about-transaction-isolation-levels/.

Also, the following contradicts what I said before (here, already removed/updated it):

https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/transaction-isolation-levels?view=sql-server-2017

Read Uncommitted: Transactions are not isolated from each other. If the DBMS supports other transaction isolation levels, it ignores whatever mechanism it uses to implement those levels. So that they do not adversely affect other transactions, transactions running at the Read Uncommitted level are usually read-only.

It describes the other states pretty well too. It sounds like Read committed would be the best here.

Read Committed: The transaction holds a read lock (if it only reads the row) or write lock (if it updates or deletes the row) on the current row to prevent other transactions from updating or deleting it. The transaction releases read locks when it moves off the current row. It holds write locks until it is committed or rolled back.

This would let the delete and the insert you mention in the comment happen safely.

Upvotes: 2

Related Questions