Optimistic and Pessimistic Concurrency

Can any body explain about the Optimistic and Pessimistic Concurrency with
practically good example.
Thanks
Noor
Noor wrote:
> Can any body explain about the Optimistic and Pessimistic Concurrency
> with practically good example.
> Thanks
> Noor
Optimistic concurrency really means that the last user to update a row
in a table is probably updating with the most accurate values. Meaning,
no forced row locking is going on to keep the record off-limits while
one user edits a row (as you could do in the old dBase world) or no
check is done at update time to check whether another user updated the
row in the interim (the RDBMS way).
Pessimistic concurrency would either keep one user at a time editing a
row or perform some checking at update time to see if the row was
changed in the interim.
There is no effective way to manually lock rows in SQL Server like you
could in the dBase days. I only say "effective" because SQL Server, like
all RDBMSs, is designed to use a more concurrent locking paradigm to
keep data available. If you keep an extended exclusive lock on data, you
may inadvertently lock an entire data page and you would certainly keep
all users from reading that information, which is bad for concurrency.
To implement pessimistic concurrency in a table, add a TIMESTAMP column
to the table. A Timestamp column automatically changes value every time
a user updates a row.
As an example:
1- User A retrieves information from a table (the column list includes
the timestamp value)
2- User A edits the information from the client application
3- User B retrieves the same row from the table (and the same timestamp
value)
4- User B edit the information from the client application
5- User B saves the information before user A. In the stored procedure,
for example, the timestamp is checked to make sure it has not been
changed. Because the timestamp has not been changed while User B edited
the information, the update succeeds and a new timestamp value is
automatically generated by SQL Server.
An example could be:
Update Tablename
Set Col1 = @.NewVal
Where Col2 = @.PK_ID
and TS = @.RetrievedTimestampValue
If @.@.rowcount = 0
Print 'Another user updated the row'
Else
Print 'Updated!'
6- User A then attempts to save the information. The update fails
because the timestamp has changed. The application could then inform the
user of this and retrieve the current values again and begin a new edit
session.
Many applications do not require timestamps and can operate sufficiently
using optimistic concurrency. That just means if User A saves the
information last (as in the example above), it is assumed that User A
has the most up to date information.
David G.
|||On Wed, 25 Aug 2004 22:00:12 -0700, Noor wrote:

>Can any body explain about the Optimistic and Pessimistic Concurrency with
>practically good example.
>Thanks
>Noor
>
Hi Noor,
I believe David's answer is wrong.
Pessimistic concurrency: when a row is read with the intent of (possible)
update, it is locked. This lock will be held until the transaction is
completed (by ROLLBACK or COMMIT). This will prevent any updates to the
row from any other connection for the entire duration of the transaction;
there's no doubt that the row will be unchanged upon update.
Optimistic concurrency: no locks are held after reading the row. If the
row is updated later, a check is made to find out if another connection
has changed the row after the row was read. If that is the case, the
update is not carried out and the user gets an error message.
In transactions that run as one batch, pessimistic concurrency is fairly
standard. But if you have an application where the current data is shown
on screen, the user changes the data to reflect changes and saves it back
to the DB, then you'll have to decide which form of concurrency to use.
Pessimistic concurrency will prevent two users from changing the same data
at the same time, but at the price of also preventing two users from
seeing the same data at the same time (unless you duplicate all screens in
order to have "browse" versions and "edit" versions); optimistic
concurrency will reduce locking problems, but at the price of the
possibility that two people try to change the same row at once (and the
one to click "save" last will get an error and will have to enter his/her
changes again).
Final note: it is also possible to not use optimistic or pessimistic
concurrency at all, but this gives a great risk. Imagine two employees
reading the data of Customer 11765 at the same time. Employee #1 changes
the address and saves the changed data back to the database, but at the
same time, employee #2 changes the name of the primary contact and clicks
save. If employee #2 saves after #1, the old address will be put bacak
into the database (and if #1 saves last, the old primary contact will be
back).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo Kornelis wrote:
> On Wed, 25 Aug 2004 22:00:12 -0700, Noor wrote:
>
> Hi Noor,
> I believe David's answer is wrong.
> Pessimistic concurrency: when a row is read with the intent of
> (possible) update, it is locked. This lock will be held until the
> transaction is completed (by ROLLBACK or COMMIT). This will prevent
> any updates to the row from any other connection for the entire
> duration of the transaction; there's no doubt that the row will be
> unchanged upon update.
> Optimistic concurrency: no locks are held after reading the row. If
> the row is updated later, a check is made to find out if another
> connection has changed the row after the row was read. If that is the
> case, the update is not carried out and the user gets an error
> message.
> In transactions that run as one batch, pessimistic concurrency is
> fairly standard. But if you have an application where the current
> data is shown on screen, the user changes the data to reflect changes
> and saves it back to the DB, then you'll have to decide which form of
> concurrency to use. Pessimistic concurrency will prevent two users
> from changing the same data at the same time, but at the price of
> also preventing two users from seeing the same data at the same time
> (unless you duplicate all screens in order to have "browse" versions
> and "edit" versions); optimistic concurrency will reduce locking
> problems, but at the price of the possibility that two people try to
> change the same row at once (and the one to click "save" last will
> get an error and will have to enter his/her changes again).
> Final note: it is also possible to not use optimistic or pessimistic
> concurrency at all, but this gives a great risk. Imagine two employees
> reading the data of Customer 11765 at the same time. Employee #1
> changes the address and saves the changed data back to the database,
> but at the same time, employee #2 changes the name of the primary
> contact and clicks save. If employee #2 saves after #1, the old
> address will be put bacak into the database (and if #1 saves last,
> the old primary contact will be back).
> Best, Hugo
Your final example is the same example I gave. It is optimistic
concurrency. And I really don't see hwo what you said is any different
than what I said.
David G.
|||On Thu, 26 Aug 2004 11:34:02 -0400, David G. wrote:

>Your final example is the same example I gave. It is optimistic
>concurrency. And I really don't see hwo what you said is any different
>than what I said.
Hi David,
What I understood from your post (but I may of course have misinterpreted
it), is that you say that:
* pessimistic concurrency uses ROWVERSION or TIMESTAMP to check for
updates by other users, and
* optimistic concurrency doesn't check for updates by others at all.
In my post, I write that:
* pessimistic concurrency completely locks rows to prevent updates by
others,
* optimistic concurrency uses ROWVERSION or TIMESTAMP to check for updates
by other users (called pessimistic concurrency in your post), and
* not checking for updates by others at all (called optimistic concurrency
at all) has no name, as this provides no concurrency control at all.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo Kornelis wrote:
> On Thu, 26 Aug 2004 11:34:02 -0400, David G. wrote:
>
> Hi David,
> What I understood from your post (but I may of course have
> misinterpreted it), is that you say that:
> * pessimistic concurrency uses ROWVERSION or TIMESTAMP to check for
> updates by other users, and
> * optimistic concurrency doesn't check for updates by others at all.
> In my post, I write that:
> * pessimistic concurrency completely locks rows to prevent updates by
> others,
> * optimistic concurrency uses ROWVERSION or TIMESTAMP to check for
> updates by other users (called pessimistic concurrency in your post),
> and * not checking for updates by others at all (called optimistic
> concurrency at all) has no name, as this provides no concurrency
> control at all.
> Best, Hugo
I would disagree with your definition of concurrency, which just means
simultaneous access to the database. I would argue that optimistic
concurrency just means that the last user to save data is the correct
one. It's optimistic because it makes the assumption the last person has
the correct data. Pessimistic means that you can never assume the last
person saving has correct information and to protect yourself from this
you either lock the row (bad with RDBMSs) or check a timestamp. In
either case, you are being pessimistic because you can't make the case
the last user has correct data.
But this is a matter of sematics really. The definitions are less
important that the reason for the implementation chosen for a given
system. If the OPs system supports a no lock, no timestamp
implementation, that is the best for performance. If not, they should
implement timestamps because the alternative of extended locking will
kill performance.
David G.
|||On Thu, 26 Aug 2004 13:06:25 -0400, David G. wrote:
(snip)
>But this is a matter of sematics really. The definitions are less
>important that the reason for the implementation chosen for a given
>system. If the OPs system supports a no lock, no timestamp
>implementation, that is the best for performance. If not, they should
>implement timestamps because the alternative of extended locking will
>kill performance.
Hi David,
I agree with that. Unfortunately (for her), the OP is not asking questions
about an implementation - she is doing a study and often asks questions to
clarify things she encountered in her books but didn't understand, or
asking self-test questions she could'n find the answer to.
So I guess that Noor is stuck with two answers of people who agree about
the different ways of concurrency control but disagree about the official
names.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||My understanding is that Hugo's definitions are correct and very well
stated! Here is what BOL says in the article entitled "Optimistic and
Pessimistic Concurrency":
Microsoft SQL ServerT 2000 offers both optimistic and pessimistic
concurrency control. Optimistic concurrency control uses cursors.
Pessimistic concurrency control is the default for SQL Server.
Optimistic Concurrency
Optimistic concurrency control works on the assumption that resource
conflicts between multiple users are unlikely (but not impossible), and
allows transactions to execute without locking any resources. Only when
attempting to change data are resources checked to determine if any
conflicts have occurred. If a conflict occurs, the application must read the
data and attempt the change again.
Pessimistic Concurrency
Pessimistic concurrency control locks resources as they are required, for
the duration of a transaction. Unless deadlocks occur, a transaction is
assured of successful completion.
David is right that definitions are 'less' important that the reason for
implementing a certain behavior, but it doesn't mean the definitions (or the
names) are not important at all. For example, when specifying attributes
when declaring a cursor through ADO, the lock type is specified as one of:
adLockReadOnly, adLockPessimistic, adLockOptimistic, or
adLockBatchOptimistic.
So knowing how ADO defines optimistic vs pessimistic is important.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:u1dsi0p51lt4f1c97n67792qc4be4qftit@.4ax.com...
> On Thu, 26 Aug 2004 13:06:25 -0400, David G. wrote:
> (snip)
> Hi David,
> I agree with that. Unfortunately (for her), the OP is not asking questions
> about an implementation - she is doing a study and often asks questions to
> clarify things she encountered in her books but didn't understand, or
> asking self-test questions she could'n find the answer to.
> So I guess that Noor is stuck with two answers of people who agree about
> the different ways of concurrency control but disagree about the official
> names.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks alot David
Noor
"David G." <david_nospam@.nospam.com> wrote in message
news:eZ5hAYziEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Noor wrote:
> Optimistic concurrency really means that the last user to update a row
> in a table is probably updating with the most accurate values. Meaning,
> no forced row locking is going on to keep the record off-limits while
> one user edits a row (as you could do in the old dBase world) or no
> check is done at update time to check whether another user updated the
> row in the interim (the RDBMS way).
> Pessimistic concurrency would either keep one user at a time editing a
> row or perform some checking at update time to see if the row was
> changed in the interim.
> There is no effective way to manually lock rows in SQL Server like you
> could in the dBase days. I only say "effective" because SQL Server, like
> all RDBMSs, is designed to use a more concurrent locking paradigm to
> keep data available. If you keep an extended exclusive lock on data, you
> may inadvertently lock an entire data page and you would certainly keep
> all users from reading that information, which is bad for concurrency.
> To implement pessimistic concurrency in a table, add a TIMESTAMP column
> to the table. A Timestamp column automatically changes value every time
> a user updates a row.
> As an example:
> 1- User A retrieves information from a table (the column list includes
> the timestamp value)
> 2- User A edits the information from the client application
> 3- User B retrieves the same row from the table (and the same timestamp
> value)
> 4- User B edit the information from the client application
> 5- User B saves the information before user A. In the stored procedure,
> for example, the timestamp is checked to make sure it has not been
> changed. Because the timestamp has not been changed while User B edited
> the information, the update succeeds and a new timestamp value is
> automatically generated by SQL Server.
> An example could be:
> Update Tablename
> Set Col1 = @.NewVal
> Where Col2 = @.PK_ID
> and TS = @.RetrievedTimestampValue
> If @.@.rowcount = 0
> Print 'Another user updated the row'
> Else
> Print 'Updated!'
> 6- User A then attempts to save the information. The update fails
> because the timestamp has changed. The application could then inform the
> user of this and retrieve the current values again and begin a new edit
> session.
> Many applications do not require timestamps and can operate sufficiently
> using optimistic concurrency. That just means if User A saves the
> information last (as in the example above), it is assumed that User A
> has the most up to date information.
>
> --
> David G.
>
|||I really appreciated to all of you guys David, Hugo, Kalen
Thanks
NOOR
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OQte737iEHA.2544@.TK2MSFTNGP10.phx.gbl...
> My understanding is that Hugo's definitions are correct and very well
> stated! Here is what BOL says in the article entitled "Optimistic and
> Pessimistic Concurrency":
> Microsoft SQL ServerT 2000 offers both optimistic and pessimistic
> concurrency control. Optimistic concurrency control uses cursors.
> Pessimistic concurrency control is the default for SQL Server.
> Optimistic Concurrency
> Optimistic concurrency control works on the assumption that resource
> conflicts between multiple users are unlikely (but not impossible), and
> allows transactions to execute without locking any resources. Only when
> attempting to change data are resources checked to determine if any
> conflicts have occurred. If a conflict occurs, the application must read
the
> data and attempt the change again.
> Pessimistic Concurrency
> Pessimistic concurrency control locks resources as they are required, for
> the duration of a transaction. Unless deadlocks occur, a transaction is
> assured of successful completion.
> David is right that definitions are 'less' important that the reason for
> implementing a certain behavior, but it doesn't mean the definitions (or
the
> names) are not important at all. For example, when specifying attributes
> when declaring a cursor through ADO, the lock type is specified as one
of:[vbcol=seagreen]
> adLockReadOnly, adLockPessimistic, adLockOptimistic, or
> adLockBatchOptimistic.
> So knowing how ADO defines optimistic vs pessimistic is important.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:u1dsi0p51lt4f1c97n67792qc4be4qftit@.4ax.com...
questions[vbcol=seagreen]
to[vbcol=seagreen]
official
>