October 21, 2003 at 9:56 am
I have a table of around 150,000 rows containing customer information. When a customer row is accessed by a particular user, I wish to preclude all other users from accessing that same row. In effect, I wish to place an exclusive lock on each accessed customer row so that only one user may edit a customer at any one time. (I’m oversimplifying this example of the customer table for clarity.)
One obvious suggestion is for the initial user to issue HOLDLOCK but this is most unsatisfactory because of the difficulty of compelling SQL to lock rows only.
Another possibility is to use a flag on the customer row which indicates whether the row is in the state of being accessed but this doesn’t work when two users attempt to access the same row at the same time.
The third option which is the one that we use is to insert the customer number into a supplemental table whenever a customer row is accessed by a user. Once the customer row is released by the user, the corresponding customer number is deleted from the supplemental table.
This third option works correctly although it provides very poor performance. Would anyone know of a more efficient method for providing exclusivity in this kind of situation, please?
October 21, 2003 at 1:08 pm
It's quite an issue to have a lock if there is user interaction required before the commit or rollback. I would suggest that you consider a synchronization routine at the time of submission to determine if someone else updated the information in the meantime. Otherwise, the user goes home and the row remains locked.
For synchronizing, I've seen the use of TimeStamp or even just a ControlNumber that is incremented on UPDATE. In both cases, the WHERE clause needs to include the original value in addition to the key values.
Guarddata-
October 21, 2003 at 5:40 pm
Have a look at BOL 2000: BINARY_CHECKSUM
quote:
Returns the binary checksum value computed over a row of a table or over a list of expressions. BINARY_CHECKSUM can be used to detect changes to a row of a table.
October 22, 2003 at 7:43 am
Jordan, You wrote:
"Another possibility......but this doesn’t work when two users attempt to access the same row at the same time."
You can try to lock by "UPDATE table SET lock=1 WHERE customerID=idNum AND lock=0"
If no records effected than the record was locked by another user.
Better add a locking user id to the table so you can go to his computer and unlock when needed.
If you have needs like this all over your application, to lock a record all the time that the user is inside some dialog, the best is to create a spacial table for locking:
CREATE TABLE [dbo].[rowlock] (
[obj_type][int] NOT NULL,
[recNum][int] NOT NULL,
[clerk][int] NOT NULL,
[lockTime][smalldatetime] NOT NULL,[computerName][varchar](25) NOT NULL, [terminalClientName][varchar](25) NOT NULL,
[appName][char](10) NOT NULL,
this constarint:
CONSTRAINT [PK_rowlock] PRIMARY KEY NONCLUSTERED([obj_type],[recNum])
will make sure that the same record (recNum), is not locked twice on the same table (obj_type).
in the Lock(obj_type, recNum) function:
INSERT INTO rowlock (obj_type, recNum, clerk, lockTime, compName, clientName, appName) VALUES (%u,%u,%ld,GETDATE(),'%s','%s','%s')"
if already locked, you will get a constraint exception.
This table containig olly the locked records will be very small.
October 22, 2003 at 12:11 pm
The table hints XLOCK is a solution
see book online for more info.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply