concurrency control sets few locks on the data – Database Management System
Optimistic concurrency control sets few locks on the data in the database, lowering the isolation level that you use in an application. This allows more applications to run concurrently and potentially increases the throughput of the applications. This strategy attempts to obtain locks on the required resources only when the transaction completes.
Pessimistic concurrency control implements a higher isolation level by placing locks on the database at the beginning of a transaction. In other words, pessimistic locking assumes there will be a collision in the database and takes precautions to prevent these collisions. A pessimistic locking strategy protects the integrity of the database by guaranteeing, from the beginning of the transaction, that no conflicting transaction can run concurrently.
The optimistic concurrency control strategy always allows a transaction to start but cannot guarantee that it will complete. The pessimistic locking strategy cannot guarantee that a transaction will be able to start, but it guarantees that, once started, any transaction can complete.
A data warehouse has many fetches, or SELECTs, and few DMLs, or UPDATEs, INSERTs, and DELETEs. An online transaction processing (OLTP) application usually has many DMLs and few SELECTs. Remember that rows selected from a partially complete transaction may not be consistent.
Discuss the following points:Identify one benefit and one drawback of each concurrency control strategy in an OLTP application. Explain.Identify one benefit and one drawback of each concurrency control strategy in a data warehouse. Explain.Suggest a scenario for a hypothetical OLTP business system where optimistic concurrency control is most appropriate and another scenario where pessimistic concurrency control is most appropriate.How would the type of user, such as a computer program, an internal business user, or a customer on the web, affect your choices?
