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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-22 : 08:15:51
|
| Steve writes "I'm in the process of modifying an old C-ISAM application to use SQL Server 7 instead. One feature I am trying to port is whereby you manually lock a record to stop people from modifying it while you're in the process of deciding what to change on it.I tried to do this by SELECTing the row with an UPDLOCK hint, which puts an IU lock on the row. This is good, as there can only be one IU lock on a row and it still allows SELECTs. However when I run an UPDATE on it it sets the row to an IX lock, blocking all operations on the row. This is no good as the record may be incrementally updated a number of times during an edit. What I would like is it to stay as IU with any SELECT statements done by other users reading it as it was before the IU lock was initiated. Any ideas how?This is the approach I am currently using in TSQL code:begin transactionselect * from [table] with updlock where [primarykey] = 'value' -- at this point the row has an IU lock and other processes can -- successfully select that table and rowupdate [table] set [stuff] = 'whatever' where [primarykey] = 'value' -- after this the row is locked with IX, and further select -- statements on the row block until the commit below.. other update commands ..commit -- now the record is unlocked" |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-07-22 : 08:37:03
|
| Hi Steve,therez nothing wrong in Sql server issuign a exclusive lock on a update. thats the way it ought to be and it is.its like this. All Read locks are sharable. All write locks are exclusive. Any Request for a write lock has to be without any existing lock on that row.in case therez any lock then the system would wait for the release and in case its not possible(like A waiting for B to release some resourses so that it can complete its job and B waiting for the same with A ) then a deadlock occours and the system kills one process among them (Again the kill is determined by lot of Algorithms). It would be a good idea if you would post what are you tring to achieve. we might able to assist you .-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson |
 |
|
|
sterobhun
Starting Member
12 Posts |
Posted - 2002-07-24 : 06:37:14
|
| Thanks for your response, it was helpful in clarifying the behaviour I'm seeing on the SQL Server.All I'm looking to do is find some way of having it not block on SELECTs on the table that is currently being updated. NOLOCK hints are no good because the data retrieved needs to be consistent. I'll probably end up using READPAST but I'm sure people will complain at some point that records are disappearing.If there's no way of retrieving the data as it was before the update started, is there a method of determining which rows are currently locked? Doing a NOLOCK SELECT which shows which rows were read that have locks on them would be useful. Perhaps there is some undocumented function that returns the lock status of the current row? Maybe two otherwise identical SELECTs with READPAST and NOLOCK could be joined to find the missing rows, but that seems like a waste running two queries instead of just one. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-24 : 08:43:01
|
The real problem is that you're porting an application that is used to dealing with individual records, while SQL Server deals with sets, not rows. Even if a set has only one row, it's still a set. It's a small distinction that has huge consequences.Another thing is that SQL Server only locks a row when you indicate ROWLOCK explicitly. It defaults to locking a page or an entire table, even if rows in that table or page are NOT part of the transaction; therefore the "lock status" of a row is meaningless in that scenario.I can see one way to simplify some of this for you. As far as incremental edits are concerned, if a user changes 10 columns of a row, all 10 columns should be updated at once (like clicking a Save button), instead of: edit 1 column, update, edit another column, update, edit the next column, update...etc. In other words, instead of 10 transactions that update a single column (and lock the row each time) you perform 1 transaction that updates all of them at once. I don't know if you're already doing it that way but if you're not I think you should.I'm not a big believer in allowing users to lock something against changes while they're browsing rows, even if they demand that kind of feature. I can usually discourage them by saying "Suppose someone locks a row and then they go to the bathroom, or go to lunch, or have to leave for some emergency. No one else can modify that row until they release it. And if they shut off their computer before they complete the transaction..." Trust me, this WILL happen and people WILL complain about it.  And they WILL NOT like to hear you say "but you ASKED for this feature..."Transactions like the ones you describe are really very short and simple affairs; grab the row, display it, let people change it on screen, and when they're done, they click a button to commit those changes. There's no need to hold a lock on a row the entire time they're making the changes. So you can just perform regular SELECT and UPDATE operations using the default locking level.If the possibility exists that someone else edits the same row at the same time, you can add a timestamp column to your table and then compare timestamps when the update is performed. Whoever missed the boat can get a warning "Someone else can type faster than you, so I didn't save your changes, try again" or something like that. And in my experience, I've had systems handle 2000-3000 transactions in a day, sometimes more, with only 1 or 2 overlapping edits. It really doesn't happen that often unless you have thousands of people using the same table. All of the transactions were done as briefly as possible, like I described above. |
|
|
|
sterobhun
Starting Member
12 Posts |
Posted - 2002-07-29 : 12:33:43
|
| Thanks for your advice, it was very useful.The way the program currently works (with no modifications whatsoever) is to lock people out of the record. The users are quite used to that although yes it does frustrate some sometimes :)How I'm doing it now is to keep a separate table of file locks (that has nothing to do with actual sql server locks). Then updates are written to a temporary table, and when the file lock is removed it updates the proper table accordingly. This seems to work so far, and most importantly allows selects on the unchanged data to take place. So thanks for the insight into that, it seems to be testing ok so far!Your timestamp idea is interesting, I need to have a think about the implications of implementing something like that though. At the moment I just want to get it working as normal, useful mods will have to come later.Unfortunately I'm quite restricted in what I can do to this program as I don't have any source code at all, just the C-ISAM file specification and file format. So it's all being done by translating files opens, reads, writes, locks etc. into sql procedures. |
|
|
|
|
|
|
|
|
Update Lock becomes an Exclusive Lock?