CODEWITHSUNDEEP
c#transactions
dara kok
dara kok

Reputation:

Dot NET and SQL Server 2005 Transaction

In a situation where i have to insert a record into a table A, and one of the fields in the table references a record in another table B. How can i make sure that until i commit the insert statement, the record in table B referenced by a the record to be inserted in table A is not tampered with.

I am thinking of including both tables into a transaction and locking all the records involved in the transaction. but that may lead to concurrency deficiency. so need your recommendation.

Thank you,

Upvotes: 2

Views: 340

Answers (4)

Marc Gravell
Marc Gravell

Reputation: 1063864

Note that even with a transaction, you;;ll need to get the isolation level right. The most paranoid (and hence the most accurate) is "serializable", which takes out locks (even range locks) when you read data, so that other spids can't play with it.

Upvotes: 1

Gary McGill
Gary McGill

Reputation: 27536

If by "tampered with" you mean deleted, then John's right, a foreign-key relationship is probably what you want. But, if you mean modified, then a transaction is the only way to go. Yes, it'll mean that you have a potential bottleneck, but there's no way to avoid that if you want your operation to be "atomic". To avoid any noticeable performance degradation, you'll want to keep the lifetime of the transaction to the bare minimum.

Since you're using c# (and presumably ADO.NET), you could use the transaction features built-into the framework. However, it's better to have the transaction handled by the databases server since this means that the transaction can be started, completed & committed in a single request (see above re transaction lifetime).

Upvotes: 0

John Saunders
John Saunders

Reputation: 161821

Yon don't need a transaction for this, just a foreign key relationship. A relationship from table A, field B_FK referencing table B's primary key will prevent the creation of the table A row if the corresponding table B row does not exist.

Upvotes: 0

pjbelf
pjbelf

Reputation: 641

If you want to make your changes to the two tables become a single atomic action then they should be performed in a single transaction. its relatively simple in .net, you just need to use the BeginTransaction method on a SqlConnection to create a new transaction and then you make your SqlCommands etc work against the transaction rather than the connection. You can also do it using a TransactionScope but you may have issues with MSDTC.

I wouldn't be to concerned about concurrency issues about using transactions. I would shy away from trying to deal with locking issues yourself, I would start with just making updates atomic and maintaining data integrity.

Upvotes: 0

Related Questions