August 14, 2009 at 5:51 am
Hi,
When user updates one column (column name - balance amount) value in a row, and other user also is trying to modify the same column ( balance amount) at the same time, the second user should nt be able to modify since the first user is modifying that particular record's amount. But the second user should able to see the record. The amount should be modified once first user operation is done. in this scenario,
should i use either ROWLOCK or UPDLOCK.
please clarify this doubt.
Regards,
Ramani.
August 14, 2009 at 6:04 am
Best to let SQL manage the locks itself. No matter what you specify, an update (any data change) must take an exclusive lock while the data is been changed. This means no one else will be able to see or modify the value while the update transaction is in process.
If you need other users to be able to read while the update is happening, then you need to use one of the optimistic concurrency models - Snapshot or Read committed snapshot. Both have overhead and can severely impact TempDB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 16, 2009 at 10:36 pm
Thank you.
Regards,
Ramani
August 18, 2009 at 3:01 am
i need to implement optimistic isolation level according to my requirement.
To avoid update conflict scenario when i use optimistic isolation level,
1. either updlock should be implemented with select stmt for isolation level
2. or read commited isolation level should be implemented (there is no code change for this)
in both cases, database should be enabled for isolation level.
am i right? please carify this doubt.
Thanks in advance.
Regards,
Ramani
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply