Which is optimistic concurrency control method

Instead, a transaction is executed without restrictions until it is committed. In optimistic methods, each transaction moves through the following phases:. Skip to main content. Start your free trial. Optimistic Methods for Concurrency Control The optimistic method of concurrency control is based on the assumption that conflicts of database operations are rare and that it is better to let transactions run to completion and only check for conflicts before they commit.

In optimistic methods, each transaction moves through the following phases: Read phase. Thus the system discards the version of the data, aborts the transaction, and returns an error message. Each time a record is updated, the version number is updated as well. This way the data that each user sees is consistent throughout the transaction, and users are able to concurrently access the database.

Note Even though the optimistic concurrency control mechanism is sometimes called optimistic locking, it is not a true locking scheme—the system does not place any locks when optimistic concurrency control is used. The term locking is used because optimistic concurrency control serves the same purpose as pessimistic locking by preventing overlapping updates. When you use optimistic locking, you do not find out that there is a conflict until just before you write the updated data.

In pessimistic locking, you find out there is a conflict as soon as you try to read the data. To use an analogy with banks, pessimistic locking is like having a guard at the bank door who checks your account number when you try to enter; if someone else a spouse, or a merchant to whom you wrote a check is already in the bank accessing your account, then you cannot enter until that other person finishes her transaction and leaves.

With pessimistic locking, the first user to request a lock, gets it. Once you have the lock, no other user or connection can override your lock.

The default concurrency control mechanism depends on the table type:. You can override optimistic locking and specify pessimistic locking instead. You can do this at the level of individual tables. One table might follow the rules of optimistic locking while another table follows the rules of pessimistic locking. Pessimistic locking requires overhead for every operation, whether or not two or more users are actually trying to access the same record.

The overhead is small but adds up because every row that is updated requires a lock. Furthermore, every time that a user tries to access a row, the system must also check whether the requested row s are already locked by another user or connection.