1

I want to insert two rows in 2 different tables but want to roll back the transaction if some pre conditions on the second table are met.

Does it work In .NET if i simply start a transaction scope and execute a sql query to check data on the second table before executing the insert statements? If so, what is the isolation level to use?

I don't want it lock the whole tables as there are going to be many inserts. UNIQUE constraint is not an option because what i want to do is guarantee not more than 2 rows in the 2nd table to have the same value (FK to a PK column of table 1)

Thanks

Van Thoai Nguyen
  • 986
  • 9
  • 22

1 Answers1

0

Yes you can execute a sql query to check data on the second table before executing the insert statements.

Fyi the default is Serializable. From MSDN:

The lowest isolation level, ReadUncommitted, allows many transactions to operate on a data store simultaneously and provides no protection against data corruption due to interruptive transactions. The highest isolation level, Serializable, provides a high degree of protection against interruptive transactions, but requires that each transaction complete before any other transactions are allowed to operate on the data.

The isolation level of a transaction is determined when the transaction is created. By default, the System.Transactions infrastructure creates Serializable transactions. You can determine the isolation level of an existing transaction using the IsolationLevel property of a transaction.

Given your requirement, I do not think you want to use Serializable since it is the least friendly for high volume multi user systems because they cause the most amount of blocking.

You need to decide on the amount of protection that is required. At a minimum, you should look into READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ. The following answer goes over Isolation Levels in detail. From that, you can decide what level of protection is sufficient for your requirement.

Transaction isolation levels relation with locks on table

William Xifaras
  • 5,212
  • 2
  • 19
  • 21