Q:

MSSQL how to properly Lock rows and insert?

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

A:

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

