Nested Transactions

SQL Server allows you to nest transactions. Basically, this feature means that a new transaction can start even though the previous one is not complete. Transact-SQL allows you to nest transaction operations by issuing nested BEGIN TRAN commands. The @@TRANCOUNT automatic variable can be queried to determine the level of nesting - 0 indicates no nesting , 1 indicates nesting one level deep, and so fourth.

A COMMIT issued against any transaction except the outermost one doesn't commit any changes to disk - it merely decrements the@@TRANCOUNT automatic variable. A ROLLBACK, on the other hand, works regardless of the level at which it is issued, but rolls back all transactions, regardless of the nesting level. Though this is counterintuitive, there's a very good reason for it. If a nested COMMIT actually wrote changes permanently to disk, an outer ROLLBACK wouldn't be able to reverse those changes since they would already be recorded permanently.

When you explicitly begin a transaction, the @@TRANCOUNT automatic variable count increases from 0 to 1; when you COMMIT, the count decreases by one; when you ROLLBACK, the count is reduced to 0. As you see, the behavior of COMMIT and ROLLBACK is not symmetric. If you nest transactions, COMMIT always decreases the nesting level by 1, as you can see illustrated in Figure 1. The ROLLBACK command, on the other hand, rolls back the entire transaction, illustrated in Figure 2. This asymmetry between COMMIT and ROLLBACK is the key to handling errors in nested transactions.

Figure 1: A COMMIT always balances a BEGIN TRANSACTION by reducing the transaction count by one.

Figure 2: A single ROLLBACK always rolls back the entire transaction.

As you can see from Figure 1 and Figure 2, you can nest transactions and use the @@TRANCOUNT automatic variable to detect the level. You also learned that COMMIT and ROLLBACK do not behave symmetrically; COMMIT just decreases the value of @@TRANCOUNT, while ROLLBACK resets it to 0. The implication is that a transaction is never fully committed until the last COMMIT is issued. No matter how deeply you nest a set of transactions, only the last COMMIT has any effect.

Reference: http://www.codeproject.com/KB/database/sqlservertransactions.aspx

Note: All this content is from internet sources.

Association, Aggregation, Composition

Association

When we have only one relationship between objects, that is called Association. Aggregation and Composition both are specialized form of Association. Composition is again specialize form of Aggregation.

Association is a relationship where all object have their own lifecycle and there is no owner. Let’s take an example of Teacher and Student. Multiple students can associate with single teacher and single student can associate with multiple teachers but there is no ownership between the objects and both have their own lifecycle. Both can create and delete independently.

Aggregation

Aggregation is a specialize form of Association where all object have their own lifecycle but there is ownership and child object can not belongs to another parent object. Let’s take an example of Department and teacher. A single teacher can not belongs to multiple departments, but if we delete the department teacher object will not destroy. We can think about “has-a” relationship.

Composition

Composition is again specialize form of Aggregation and we can call this as a “death” relationship. It is a strong type of Aggregation. Child object dose not have their lifecycle and if parent object deletes all child object will also be deleted. Let’s take again an example of relationship between House and rooms. House can contain multiple rooms there is no independent life of room and any room can not belongs to two different house if we delete the house room will automatically delete. Let’s take another example relationship between Questions and options. Single questions can have multiple options and option can not belong to multiple questions. If we delete questions options will automatically delete.

Another example, a university owns various departments (e.g., chemistry) , and each department has a number of professors. If the university closes, the departments will no longer exist, but the professors in those departments will. Therefore, a University can be seen as a composition of departments, whereas departments have an aggregation of professors.

Reference: http://geekswithblogs.net/mahesh/archive/2006/11/03/95960.aspx