| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2005-06-17 : 05:00:11
|
| I need a specific field in a (single) specific record to hold a specific value while I perform some processing. My question is can I generate a lock and hold it without locking anything else in the record.The particular record should be the only one in this table so in theory the field could be locked.What I need is something likebegin transactionSELECT MYFIELD FROM MYTABLE WITH <FIELDLOCK>do a load of stuffUNLOCK FIELDCOMMIT/ROLLBACK TRANSACTIONthankssteveA sarcasm detector, what a great idea. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-06-17 : 06:48:49
|
emmm.... no.never heard of column locking... but i'm still young...  Go with the flow & have fun! Else fight the flow  |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-06-17 : 07:05:54
|
| OK I think I've mostly worked out how to do what I want to here. BOL is suggesting the only lock types are :_DB = DatabaseFIL = FileIDX = IndexPG = PAGEKEY = Key TAB = TableEXT = ExtentRID = Row identifierbut I don't think I understand the TRANSACTION ISOLATION LEVELs very well. I get the impression I need to set transactions to repeatable read or higher but can I do this for implicit transactions? i.e. ones from my (legacy) app which is MS Acess 97?thankssteveAlright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-17 : 07:16:05
|
| KEY locks are taken on index keys. RID locks are similar but apply only to non-clustered tables. Both could be equated to row locks, which is the smallest unit you can lock in SQL Server. You cannot lock a column during a transaction.I imagine that you're trying to keep the eager MS Access minions from editing the same row at the same time? I won't say it's impossible, but I know I never had much luck with it. Not to mention the ever-escalating problem of idiots going to lunch while leaving the table locked. I pretty much gave up and came to the conclusion that it was not necessary 99.9% of the time anyway, and in the very rare case that it was, it was easier to rewrite the app or procedure so that it became completely unnecessary. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-06-17 : 08:20:30
|
| Thanks Rob that's really helpful, your assumptions are spot on (specially the bit about lunch, though to be fair to the poor little users the app does encourage them to leave the table locked until they get around to clicking a button, so waht difference does it make if they go for lunch, or leave for the day/week etc).I think I'll do the same as you and keep my fingers crossed and hope that no one notices. We only have a relatively modest number of users at a time so it shouldn't be too much of a problemWith regard to the type of locks it's all a bit strange in this table as there is only one record so no point in an index and effectively a row lock is a table lock, are table locks still more efficient in these circumstances? I would have thought not but have no evidence either waysteveAlright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-17 : 08:48:46
|
| Hehehehehe, yeah, locking a one-row table is an interesting debate on the actual level of locking.Table locks consume fewer resources than page locks, which consume fewer resources than row locks. A one-row table won't matter, but million-row tables obviously would. You will still have KEY locks if there are any indexes on the columns you're manipulating |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-06-20 : 09:39:38
|
We've done this in the past using a "Resource" tableThe problem we were trying to solve was to allow locking of a "resource" whilst a user updates it. Database locks are not good for this because they ought not to be "held" for this length of time. I figure that's something like the problem you are facing?So for us it went something like:User_A needs Resource_1Check is Resource_1 is locked, and if not "create" a lock(If already locked show the User/Time when it was locked)Allow user to modify record, then:BEGIN TRANSACTIONPerform DB actionsIF ERROR or RESOURCE is NOT still locked by me ROLLBACK Return error code so that Application "Fails"ELSE Clear the Lock (WHERE OWNER = ME) [1] COMMITEND [1] This allows a situation where the lock was released just as we were getting to be done, don't accidentally release the resource if now owned by a different user!!This is in part designed so that the Lock can be taken away from the user - if they go to lunch, and someone needs the lock, then it can be removed. When the user comes back from lunch, if they just "SAVE" it will get rolled back when the system discovers they don't own that resource [any more]For this to work it is crucial that all WRITE operations go via this "resource locking" process.Reading isn't really effected - you need to be sure you are "locked" before doing a read which is an integral part of getting date for the user to then modify and subsequently Save, but not, for example, for a Report - the UPDATES & DELETES are still atomic.A "Resource" for us was Table + PK data, but could easily e Table + Column + PK Data. To that was appended the "Owner". NOTE: The owner must equate to a Machine Session - Login ID is no user if the user could login from two different machines.The main benefit, as I see it, is that the Client Apllication can show Who and When something was locked, AND provide an end-user tool to "release" a deadlocked resource. (Plus, probably, a scheduled tool that removed resource locks after, say, one hour). Its basically a [Distributed] Sempahore system.You should try to Lock Resources in pre-determined order, otherwise you will be get Race conditions, which will cause deadlock (and you won't have the benefit off SQL's Deadlock resolution tool!)Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-06-21 : 03:07:18
|
| Thanks Kristen you've given me some good ideas heresteveAlright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-06-21 : 14:12:49
|
| I like to see you kept busy ....... its my tax payer's money, after all!!Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-06-22 : 03:13:44
|
I can't argue with that! It seems to have quite an effect when we're buying stuff. I sometimes like to say "Ooh that's a bit more than we thought, never mind it's your money isn't it?" You would be amazed how effective it can be Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer. |
|
|
|
|
Field frenzy