Could anyone explain what pessimistic and optimistic is in database terminology?

Question

I would appreciate it if you'd give me some real life examples of pessimistic databases or optimistic databases.

[pessimistic/optimistic databases? Are there such things?]

Answer 1

Pessimistic and optimistic generally applies to the type of locks placed on a recordset.

A recordset (sometimes called a result set) is a group of records, based on some criteria, that you get from a database table. For example:

SELECT * FROM mytable WHERE myname = 'Jones'

Would return a recordset.

If you're just going to read out those records and display them on a Web page, for example, then the type of lock you use doesn't really matter.

However, if you intend to modify (edit) those records, or delete them, then the type of lock you use does matter.

The problem is, if you are editing or deleting records, what happens if someone else comes along and wants to look at the same recordset, or wants to look at the records you are using as part of a different recordset? Worse, what if they, too, want to edit or delete records you are editing or deleting?

What you do to the records while editing or deleting them directly impacts the records other users get, and vice-versa. Thus, we use locks -- restrictions on the records in a recordset -- to dictate how the database should handle requests for the same records.

With a pessimistic lock, we assume someone else is going to try to come along and get the same records, with the intent of also editing or deleting them. So, we say to the database, "I intend to do harm to these records by modifying or deleting them. Other users should not be allowed to work with these records until I am done mauling them."

In most databases, a pessimistic lock won't even allow other users to view those records, nonetheless edit or delete them, as long as the lock is in place. (The lock is released when we explicitly inform the database to release the lock; or, if we never do, generally speaking, after a default period of inactivity with the records.)

An optimistic lock works around that issue by basically saying, "I am going to modify or delete these records, but I am fairly certain noone else will come along and do the same while I'm working with these records, so don't worry about it."

So long as noone else does come along and modify the records, we're fine. But if they do, our attempts to modify the same records can cause errors -- such as if we try to delete a record someone else has already deleted, or modify a record that has been deleted, or modify a record by using a key value that has changed, etc.)

The benefit of an optimistic lock is that generally speaking, users can view the same records with which we're working, as we work with them.

Answer 2

Optimistic/pessimistic refers to the concurrency control mechanism used by a database.To understand this first you need to know what concurrency is and what are the problems arising out of it.

Concurrency: Multiple people trying to simultaneously access shared entities(data records in case of databases).

Collisions may occur when multiple access to the same data record is attempted(I try to read the data while you are still changing it)

To handle such issues you need Concurrency control. There are two types of concurrency control mechanisms:
Pessimistic locking and Optimistic locking

Pessimistic locking: This assumes that a collision will definitely occur (pressimistic)and tries to prevent it by locking the record in use, i.e. while a record is being used no one else can use it.

Optimistic locking: This assumes that the collisions will not occur(optimistic) and tries to detect and resolve a collision if it occurs at all.

A database may use pessimistic locking or optimistic locking or both.

Answer 3

See pessimistic and optmistic in DBMS are realted to acquiring locks over a shared resource
A pessimistic would opt for an exclusive lock even when he requires shared or read only locks

Answer 4

umm a database contains information...information is unbiased

Answer 5

i guess it means positive and negative or male and female etc.