Concurrency and Types

What is Concurrency ?

It can be defined as the multiple process to access or change the data at the same time.
Concurrency can be avoided by using Isolation levels.

For details about Isolation levels see my post
http://ravisql2k5.blogspot.com/2010/10/isolation-levels.html

There are two types of Concurrency models in SQL Server 2005 and 2008
Pessimistic and Optimistic
Pessimistic concurrency model is available before SQL Server 2005 and Optimistic is introduced in SQL Server 2005.
Pessimistic : It is the default behaviour of SQL Server to acquire locks and to block access to data that other process is using.In other words we can say readers block writers and writers block readers in pessimistic concurrency environment.
Pessimistic concurrency model supports 4 types of Isolation levels.
Read committed, Read Uncommitted, Repeatable Read, and Serializable

Optimistic Concurrency Model :

This is newly Introduced from SQL Server 2005 and it supports to 2008 also.The default behaviour of this model is to use row versioning.
we can say writers do not block readers and readers do not block writers but writers can block writers.
Optimistic supports two types of Isolation levels.
Read committed snapshot and Snapshot Isolation levels.
Both uses row versioning.
To see the details of Isolation levels check my below post
http://ravisql2k5.blogspot.com/2010/10/isolation-levels.html