Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Locking a record in Edit Mode Expand / Collapse
Author
Message
Posted Sunday, October 20, 2013 7:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 2:21 PM
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???
Post #1506527
Posted Sunday, October 20, 2013 11:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:42 AM
Points: 3,440, Visits: 5,398
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!
Post #1506535
Posted Monday, October 21, 2013 3:08 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:10 AM
Points: 526, Visits: 1,163
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...
Post #1506590
Posted Monday, October 21, 2013 4:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:42 AM
Points: 3,440, Visits: 5,398
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!
Post #1506602
Posted Monday, October 21, 2013 5:24 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:10 AM
Points: 526, Visits: 1,163
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...
Post #1506628
Posted Monday, October 21, 2013 6:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #1506653
Posted Monday, October 21, 2013 6:41 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:10 AM
Points: 526, Visits: 1,163
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...
Post #1506658
Posted Monday, October 21, 2013 6:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #1506665
Posted Monday, October 21, 2013 7:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:42 AM
Points: 3,440, Visits: 5,398
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!
Post #1506681
Posted Monday, October 21, 2013 9:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #1506719
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse