/    Sign up×
Community /Pin to ProfileBookmark

Locking a Record in a Database

Copy linkTweet thisAlerts:
Jun 18.2009

I need to know how to lock a record in a database so two people can’t edit it at the same time, but they need to still be able to view it. I’ve thought about putting a field in the database that if they go to edit the record it puts a flag in it that I can then use to not allow others to edit it. That’s easy enough on the front end, but potentially problematic if the fields value doesn’t get cleared out after they’ve left the page or left the site. I know you can use the global.asa file to handle some of this, but I’ve never been able to get this to work 100%. It’s always been somewhat faulty. Does anyone have any ideas on the best way to handle this? Thanks!

to post a comment

3 Comments(s) ↴

Copy linkTweet thisAlerts:
@svidgenJun 18.2009 — Row-level locking is generally handled by the DBMS by means of transactions. You ought to re-post your question, stating which DBMS you're using, in the SQL section. Though, I bet if you Google for [I]"<your DBMS> transactions"[/I] you'll find what you need.
Copy linkTweet thisAlerts:
@nbcrockettauthorJun 18.2009 — I'm using an Access database and I looked up transactions, but I'm unsure of how they would actually apply here since my transaction spans across multiple pages.
Copy linkTweet thisAlerts:
@svidgenJun 18.2009 — Oh, you're looking lock a row between HTTP requests?

Presuming that's it, it may be best to combine the built-in transaction feature with an application level locking mechanism. You'll probably want to add a lock-date-time and a lock-session-id to the table(s) you need to lock. Then, when you need to write to a row, check the row's lock-date-time to see if the "current lock" is still valid (based on however long you want your locks to be valid for). If it is, check to see if the lock-session-id belongs to the current request. And of course, to actually lock a row, set the lock-date-time to the current date-time and set the lock-session-id to a unique session id. And when you need to explicitly unlock a row before the lock expires, simply set the date-time to [I]00/00/00 00:00:00[/I] [I]OR[/I] [I]current time - max lock length - 1 second[/I].

And of course, some of those multi-step things can be done in a single query ... like checking the lock time and session_id ... that's a single query.

Don't forget to perform all these operations within the context of a transaction though.
×

Success!

Help @nbcrockett spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 4.24,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...