Concurrency?

How do you handle this today?
I am mapping out a new system and was interested in a fresh approach.
__Stephen
That's a big topic. In SQL Server 2005, concurrency has been enhanced with
snapshot isolation, as well as database snapshots. With older versions, you
can use dirty reads, optimistic locking and READPAST.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:esteMwm%23FHA.924@.TK2MSFTNGP12.phx.gbl...
> How do you handle this today?
> I am mapping out a new system and was interested in a fresh approach.
> __Stephen
>
|||Is there a specific problem you're having? Why aren't SQL Server's built-in
facilities for handling concurrency good for your situation?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:esteMwm%23FHA.924@.TK2MSFTNGP12.phx.gbl...
> How do you handle this today?
> I am mapping out a new system and was interested in a fresh approach.
> __Stephen
>
|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uSTN9Bn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
> Is there a specific problem you're having? Why aren't SQL Server's
> built-in facilities for handling concurrency good for your situation?
>
What I have is the statement created in my biz layer and presented back to
the db with an "and" stipulation.
update
MyTable
set myChangedColumn = NewValue , ..
where Pkey = ThisKey
AND myChangedColumn = OldValue , ..
Thus I'm only changing what the user has changed, and it won't whack someone
elses update who also changed the same value.
Is there a better way to do this?
My GUI is ASP or ASP.NET and mostly ASP today
__Stephen
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OvmQ4Bn%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
> snapshot isolation, as well as database snapshots. With older versions,
> you can use dirty reads, optimistic locking and READPAST.
Do you mean, "pessimistic" locking?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||Nope, optimistic. Optimistic locking allows you to release the lock right
after you read it. Then, when you go to update it, if the timestamps
(rowversions) don't match, then you can retrieve the current version. That
provides more concurrency than when you keep the row locked until the user
has finished their update.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23sN69Kn%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OvmQ4Bn%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
> Do you mean, "pessimistic" locking?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
|||"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:u2re6Jn%23FHA.1028@.TK2MSFTNGP11.phx.gbl...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uSTN9Bn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
> What I have is the statement created in my biz layer and presented back to
> the db with an "and" stipulation.
> update
> MyTable
> set myChangedColumn = NewValue , ..
> where Pkey = ThisKey
> AND myChangedColumn = OldValue , ..
> Thus I'm only changing what the user has changed, and it won't whack
> someone elses update who also changed the same value.
> Is there a better way to do this?
>
You can add a timestamp column to the table to simply the stipulation, but
basically you've got it right. It's not generally practical to use
pessimistic concurrency from clients like ASP.NET, so client-generated
optimistic concurrency is the norm.
David
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23eeN1Nn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
> Nope, optimistic. Optimistic locking allows you to release the lock right
> after you read it. Then, when you go to update it, if the timestamps
> (rowversions) don't match, then you can retrieve the current version.
> That provides more concurrency than when you keep the row locked until the
> user has finished their update.
Right, but the important point is, SQL Server 2000's isolation levels are
all pessimistic. Applications can implement an optimistic scheme -- but
this is the application doing so, not SQL Server.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||Right. I was assuming he was asking for ideas on how to minimize
concurrency problems.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O$6XNao%23FHA.3296@.TK2MSFTNGP10.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23eeN1Nn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
> Right, but the important point is, SQL Server 2000's isolation levels are
> all pessimistic. Applications can implement an optimistic scheme -- but
> this is the application doing so, not SQL Server.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>