Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
leroy1
Starting Member
2 Posts |
 Posted - 2004-11-04 : 07:59:18
|
| I'm working on a multi-user app.But now i want to lock a row in a table if it is in use.it must be locked so that the other users can only read the file.but can't edit it. who can help me???ps. the table's, queries, sp's etc. Are in SQL the front end is Access |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-04 : 08:04:16
|
| I would have a table to hold the locked row.Before an update the user has to grab the resource.In the table hold the spid and login time. If another user tries to get the row then allow it if the locking spid/login time doesn't exist (means spid has crashed without releasing).You will also need a task to clear up dead entries.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
leroy1
Starting Member
2 Posts |
Posted - 2004-11-04 : 08:10:57
|
I was Thinking of a time/date field that would expire after an interval. And wen the time/date would start a lock would start on the row. and when the interval expired the lock would drop and the user get's a time out message??but i don't know how i want to do this yet I know it must be a boolean field. but that's it. that's all i know. who know's how i can do this |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-04 : 08:37:29
|
| you could do that byupdate tblset lockdte = getdate(), lockspid = @@spidfrom locktimeoutwhere id = @idand (lockdte is null or locdte <= getdate() - locktimeout.lockperiodif @@spid = (select lockspid from tbl where id = @@id) select 'lock succedded'else select 'lock failed'Then just check the spid when the update takes place.The usual way to do this is to have a timestamp col on a table - get the timestamp when retrieving the rec then check it on update. If the timestamp is different then reject the update.This is fine unless the entity that is being updated is spread across several tables in which case having another table to hold the lock might be better. I suggested a separate table so that several entity types can use the same method.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
GunZ
Starting Member
29 Posts |
Posted - 2004-11-04 : 22:53:53
|
quote:
Originally posted by nr
you could do that byupdate tblset lockdte = getdate(), lockspid = @@spidfrom locktimeoutwhere id = @idand (lockdte is null or locdte <= getdate() - locktimeout.lockperiodif @@spid = (select lockspid from tbl where id = @@id) select 'lock succedded'else select 'lock failed'Then just check the spid when the update takes place.The usual way to do this is to have a timestamp col on a table - get the timestamp when retrieving the rec then check it on update. If the timestamp is different then reject the update.This is fine unless the entity that is being updated is spread across several tables in which case having another table to hold the lock might be better. I suggested a separate table so that several entity types can use the same method.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
It's good for lookup tables (eg. only 8 columns or less), but what if I'm updating only 1 out of 27 columns being shared by 14 possible connections? It will prevent other connections from updating columns that 1 connection isn't using at all.Australia.NSW.Sydney.GunZ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-05 : 08:00:49
|
| If a connection is updating a row, the ROW is locked. Columns are not divisible units of a row, they are all accessed by the connection. You cannot lock one column and leave others unlocked for a separate connection to update. Having such a feature would be the easiest way to corrupt data. |
|
|
|
|
|
|
Multi-user row locking