thoughts on recordlocking

[Crazy D @ Work]

Hi,
My boss wants me to lock a record in SQL Server 7 when a select query is done (we don't have a special edit mode - user can change the record and it will be written when he moves to another record).
Since this is not really "normal", it's "normal" to lock when you write, not read, I was wondering what you're thinking of that (and besides, it's impossible to lock 1 record while reading without using extra fields (lock-boolean).
Since he pays me I continue searching for another way to lock then using the extra field, but I'm not really happy with this... why would someone want to lock when reading anyway?
Gimme your opinions... (hehe and some good arguments to change the mind of my boss... :-))
Happy coding ;-)

Crazy D @ Work :-)

[Aaron Young]

I can't imagine why he wants to lock on a Read..
I've always found doing things to work around an Idea that's not really well founded, (i.e Locking on a Read), always leads to more difficulties down the road.

I'd remind him Why he pays you, then do it your way.

:)


Aaron Young
Analyst Programmer
adyoung@win.bright.net
aarony@redwingsoftware.com

[Crazy D]

The only reason I can think of because it's the way the dos-version of the program works... duhh what a reason *L* hehe but I'm glad I'm not the only one who's thinking it's not a great idea to lock on read :-)
Hehe and why he pays me? That's what I'd like to know either ;-)

Crazy D :-)
"One ring rules them all"

[BrewGuru99]

Mabey your boss is thinking that the data read may need to be edited after reading it all, and he probably wants the data in the recordset to be as current as possible.

Just my 2 cents. Still, I don't see this as a very strong argument...

Brewguru99

[Crazy D @ Work]

He wants to be sure that if user A changes some address info (for instance) and user B is making invoices, that the info is correct. But that's definitely not a strong argument, but it's the way DOS works and he wants that... I'll try to convince him it's a kinda bullsh*t reason... and it's just not possible.. not without extra fields (and it's obvious it's not possible sine it's against all logical database rules...)
Well we'll see :-)
For now I can surf the web and get paid *LOL*

Crazy D @ Work :-)

I have a similar situation, but in this case I guess locking the record exclusively during read makes sense.
You see, I have a table that has the control number field, if userA needs a control number the program will access the table &
will get the first record available & lock it exclusively & the record on this table will be deleted if the transaction is confirmed.
But while the transaction is still in progress I need to know w/c record is being used, because If another user also needs a control
number the program should get the next available record that is not being accessed or locked by other users, this way two or more users
will be sure of getting different control numbers.
Somebody suggested that a stored procedure can possibly do this, but I don't know how that is, there is no locked() function that returns
that lock status of the record from a table in SQL/SERVER that I can include in the where clause of the SQL command.

Any luck so far???

Thanks,
Drake

[Crazy D]

The way I solved it (I wasn't allowed to use extra fields) is read the fields when go into edit-mode, and before writing, check if it has changed (similar when you add an Timestamp field), and we'll notify if the data has been changed in the meanwhile.
For you Drake, I'd say add a new record when you got the new number, that way, when the next user wants to add a new record, the number he'll get is never the same as yours.

Crazy D :-)
"One ring rules them all"