Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Locking a record in Edit Mode


Locking a record in Edit Mode

Author
Message
PradeepVallabh
PradeepVallabh
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 526
I have an ASP.NET(code behind C#) web application and i need to lock a particular record when a user is in Edit mode. I know we can create a separate table which inserts the records that are being edited and we can check everytime when a user is trying to edit...

Any other better solutions???
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4255 Visits: 6431
I think you're going to have issues doing this no matter how you choose to "lock" the records, because if the user closes the web form while a record is locked, it is likely to leave that record locked until you have some process come along and remove the lock.

A better way might be to simply warn the user/block the save if the record changed during their edit session by comparing the values retrieved to that user originally with the same values retrieved from the row just before the save.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
There's a bad way to accomplish that..
Just use XLOCK... XLOCK makes an exclusive lock to the record until the transaction is committed...
On c# you can:
1. begin transaction
2. SELECT 1 FROM tableToLock WITH (XLOCK) WHERE Id = @ID
3. do what ever you want....
4. commit transaction

But it's a risky thing to do...

Pedro



If you need to work better, try working less...
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4255 Visits: 6431
PiMané (10/21/2013)
There's a bad way to accomplish that..
Just use XLOCK... XLOCK makes an exclusive lock to the record until the transaction is committed...
On c# you can:
1. begin transaction
2. SELECT 1 FROM tableToLock WITH (XLOCK) WHERE Id = @ID
3. do what ever you want....
4. commit transaction

But it's a risky thing to do...

Pedro


Sounds like you want to open a transaction when you read the record for update in the web form? Then keep that transaction open until the web form saves? What happens if they close the browser without saving?

Not sure how advisable that is.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
dwain.c (10/21/2013)
Sounds like you want to open a transaction when you read the record for update in the web form? Then keep that transaction open until the web form saves? What happens if they close the browser without saving?

Not sure how advisable that is.


Thanks why I said it's risky and not advisable...
The transaction, when the browser closes, can also be closed or opened and closes on a timeout...
This has to be configured on the config file (app.config if application, web.config if web site)..

Pedro



If you need to work better, try working less...
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47255 Visits: 44387
dwain.c (10/21/2013)
Not sure how advisable that is.


It's strongly not recommended. Transactions should be started and committed/rolled back in a single trip to the database, not left open during user interaction. Doing so is asking for severe blocking and deadlock problems.

Worse than closing the browser, imagine if they leave the browser open (with the transaction open), go for a long lunch, don't look at the browser after lunch then go away for a long weekend.

Pradeep, one way you can do this is optimistic concurrency. Assume that in most cases someone else won't want to change the same record. Have the app check the column values when the edit starts and then check them again when the user's done. If there's a difference, then and only then alert the user that someone else has modified the same row they were working on and give them options on how to handle that.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
What I did in a situation where the user couldn't update data or if I wanted to update data changed was by "assuming" it was to add a column on each table named RecordVersion.
When the user reads the data gets version X.
If someone meanwhile reads and updates the data the version will be X+1.
When the 1st user sends the data a warning will be shown since the update requires RecordVersion = X and it's not.
He is warned that the data has been changed since he read it and if he wants to commit it anyway..
It requires a little more of database programming and the application but it's safer...

Pedro



If you need to work better, try working less...
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47255 Visits: 44387
You can do that, the row_version data type's great for that as you don't have to do manually update it. I've also seen people use a calculated column defined as Checksum(*)


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4255 Visits: 6431
GilaMonster (10/21/2013)
dwain.c (10/21/2013)
Not sure how advisable that is.


It's strongly not recommended.


I was being polite. Not meaning that you're being rude of course. But you've got better creds than me to be so clear and pointed.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47255 Visits: 44387
dwain.c (10/21/2013)
GilaMonster (10/21/2013)
dwain.c (10/21/2013)
Not sure how advisable that is.


It's strongly not recommended.


I was being polite.


So was I. :-)


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search