Need help with optimistic concurrency in database

Hi all, Could somebody pls explain with simple example in three tier architecture how optimistic concurrency work in sql database????? And which data provider is used to connect asp.net with oracle 9i database. Thanks, Sumit