Link to home
Start Free TrialLog in
Avatar of sainavya1215
sainavya1215

asked on

Timestamp and difference of sqlserver locking

hi,

I have developed an multiUser application in .NET with sqlserver 2000 and stored procedures

regarding updates of tables in stored procedure
========================================
When retrieving a record for editing/update i get the timestamp column and when updated check the timestamp
column of the table with the retrieved one compareboth if not the same ie @@rowcount=0
display an error message saying record has been modified by another user.

APPLICATIONS WORKS FINE.

I have 2 doubts about this

1) Timestamp column in used in every table in our project
   Is there any alternative other than timestamp column considering a multiuserApp or is this the best approach we are following

2) when we are using timestamp column for updates etc should I care about using

 sqlserver's locking mechanisms like these

  Deadlocking
Set Deadlock_Priority
Transactions:
    Isolation Levels
    Locking
    ACID properties (Atomicity, Consistency, Isolation, Durability)
SOLUTION
Avatar of arbert
arbert

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sainavya1215
sainavya1215

ASKER

I am using Timestamp dataType in every table.

Column              dataType
Timestamp       Timestamp
Good, I figured you really meant a timestamp.  I don't see a problem with what you're doing at all--sounds perfect.
ok ..fine..But my doubt is why cant all use the same timestamp technique instead of going for rowlock etc in stored proc at sqlserver level... I am bit confused with that.
and other question is lets say I dont implement a timestamp column in my tables what is the other way of updates deletes consdiering a mutiuser application ..
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok thanks..

Arbert had put forth a question earlier Are you actually using a binary timestamp or a datetime column that serves as a timestamp?

which of these is best to use ? currently I am using datatype as timestamp........how abot if one implements dateTime as timestamp.

any advantages?
I think changing to datetime columns will not have much affect for your case.   However, if someone happens to update your rec within ONE MICROSECOND (or whatever accuracy your SQL keeps) of your having fetched it, you won't detect the clash, while a true timestamp is guranteed never to miss a clash.  However, this is extremely unlikely to say the least.  There's no advantage to changing, unless you want to sort records by the time of most recent change.  (the value of a real timestamp cannot be sorted usefully)  However, you can do that with a separate datetime column with a trigger to set the value on UPDATE.

Cheers

Geoff
"while a true timestamp is guranteed never to miss a clash"

I don't think you can say guaranteed--in an ideal world yes--in reality no.  Probably wouldn't ever see it happen, but there could be a chance of a rollback and the timestamp wouldn't change.  Still more foolproof IMO than a datetime col.
"ok ..fine..But my doubt is why cant all use the same timestamp technique instead of going for rowlock etc in stored proc at sqlserver level... I am bit confused with that."

You do realize, regardless of timestamp or not, SQL Server is still going to take a lock during the update--right?