|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Data integrityDoes any one know how to apply/enhance the database's integrity. During study, i learn the theory of how to make sure the data retrieve and update is correct. But i dont know how to apply in database development. For example: I have a table. 2 persons are accessing the table in the following sequence: Person A, access the table. Person B, access the table. Person A update new value (such as rental_fee) Person B also update the rental_fee value. Person A exit Person B also exit At this moment, record updated by person A has been overwrited by person B. How to prevent it? Thanks in advance. Best regards, GL One method is to use optimistic concurrency. This technique checks to see
if the original value was modified by another user: > Person A, access the table. SELECT @OldRentalFee = RentalFeeFROM RentalProperties WHERE RentalPropertID = 1 > Person B, access the table. SELECT @OldRentalFee = RentalFeeFROM RentalProperties WHERE RentalPropertID = 1 > Person A update new value (such as rental_fee) UPDATE RentalPropertiesSET RentalFee = @NewRentalFee WHERE RentalPropertID = 1 AND RentalFee = @OldRentalFee IF @@ROWCOUNT = 0 BEGIN RAISERROR('Data was updated by another user', 16, 1) END --the above succeedes > Person B also update the rental_fee value. UPDATE RentalPropertiesSET RentalFee = @NewRentalFee WHERE RentalPropertID = 1 AND RentalFee = @OldRentalFee IF @@ROWCOUNT = 0 BEGIN RAISERROR('Data was updated by another user', 16, 1) END --the above raises an error A common practice to use a rowversion data type (formally timestamp) for the concurrency check. This simplifies concurrency checking because the value is automatically changed by SQL Server whenever any row value changes and you don't need special handling of NULLs. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Daniel" <Dan***@discussions.microsoft.com> wrote in message news:E6002470-2152-41AC-AE2F-D33A1676CCFA@microsoft.com... > Hi, > Does any one know how to apply/enhance the database's integrity. > > During study, i learn the theory of how to make sure the data retrieve and > update is correct. But i dont know how to apply in database development. > For example: I have a table. 2 persons are accessing the table in the > following sequence: > Person A, access the table. > Person B, access the table. > Person A update new value (such as rental_fee) > Person B also update the rental_fee value. > Person A exit > Person B also exit > At this moment, record updated by person A has been overwrited by person > B. > > How to prevent it? > > Thanks in advance. > > Best regards, > GL Thanks for ur help.
I have another question, do i need to configure the sqlserver 2000 in order to do that? In addition, creating a column (auto generate number) act as one of the primary key is a good practice? Show quote "Dan Guzman" wrote: > One method is to use optimistic concurrency. This technique checks to see > if the original value was modified by another user: > > > Person A, access the table. > > SELECT @OldRentalFee = RentalFee > FROM RentalProperties > WHERE RentalPropertID = 1 > > > Person B, access the table. > > SELECT @OldRentalFee = RentalFee > FROM RentalProperties > WHERE RentalPropertID = 1 > > > Person A update new value (such as rental_fee) > > UPDATE RentalProperties > SET RentalFee = @NewRentalFee > WHERE RentalPropertID = 1 AND > RentalFee = @OldRentalFee > IF @@ROWCOUNT = 0 > BEGIN > RAISERROR('Data was updated by another user', 16, 1) > END > --the above succeedes > > > Person B also update the rental_fee value. > > UPDATE RentalProperties > SET RentalFee = @NewRentalFee > WHERE RentalPropertID = 1 AND > RentalFee = @OldRentalFee > IF @@ROWCOUNT = 0 > BEGIN > RAISERROR('Data was updated by another user', 16, 1) > END > --the above raises an error > > A common practice to use a rowversion data type (formally timestamp) for the > concurrency check. This simplifies concurrency checking because the value > is automatically changed by SQL Server whenever any row value changes and > you don't need special handling of NULLs. > > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Daniel" <Dan***@discussions.microsoft.com> wrote in message > news:E6002470-2152-41AC-AE2F-D33A1676CCFA@microsoft.com... > > Hi, > > Does any one know how to apply/enhance the database's integrity. > > > > During study, i learn the theory of how to make sure the data retrieve and > > update is correct. But i dont know how to apply in database development. > > For example: I have a table. 2 persons are accessing the table in the > > following sequence: > > Person A, access the table. > > Person B, access the table. > > Person A update new value (such as rental_fee) > > Person B also update the rental_fee value. > > Person A exit > > Person B also exit > > At this moment, record updated by person A has been overwrited by person > > B. > > > > How to prevent it? > > > > Thanks in advance. > > > > Best regards, > > GL > > > Dan's code will work straight out of the box. There is no extra SQL
Server config needed. Timestamp is a native SQL datatype, so you don't need to reconfigure anything to use the timestamp for the row versioning either. With regards to using an identity column (auto generated sequential integer) as a primary key for a table, there are various debates about that. Many people would say that's often the best way to define primary keys in real life, while others (the most vocal being one Joe Celko, who has spent some time on the ANSI committee and published some material on the subject) would disagree and advise you to use a natural key as your primary key for the table (like a SSN or VIN or ActionType/ActionDate combination for example). I won't get into the whole debate (it's a rather raging topic) except to say both sides of the argument can be justified. If you do create a clustered index on an identity column, however, you can substantially reduce pages splits (and therefore I/O) on inserts because all new data will be located at the end of the index and no reordering within the index will be necessary. This will create a "hotspot" in effect at the end of the index. But there are many other factors to consider, not the least of which is personal preference, when declaring one or more columns to be the primary key of a table. Show quote >Thanks for ur help. >I have another question, do i need to configure the sqlserver 2000 in order >to do that? >In addition, creating a column (auto generate number) act as one of the >primary key is a good practice? > > >"Dan Guzman" wrote: > > > >>One method is to use optimistic concurrency. This technique checks to see >>if the original value was modified by another user: >> >> >> >>>Person A, access the table. >>> >>> >>SELECT @OldRentalFee = RentalFee >>FROM RentalProperties >>WHERE RentalPropertID = 1 >> >> >> >>>Person B, access the table. >>> >>> >>SELECT @OldRentalFee = RentalFee >>FROM RentalProperties >>WHERE RentalPropertID = 1 >> >> >> >>>Person A update new value (such as rental_fee) >>> >>> >>UPDATE RentalProperties >>SET RentalFee = @NewRentalFee >>WHERE RentalPropertID = 1 AND >>RentalFee = @OldRentalFee >>IF @@ROWCOUNT = 0 >>BEGIN >> RAISERROR('Data was updated by another user', 16, 1) >>END >>--the above succeedes >> >> >> >>>Person B also update the rental_fee value. >>> >>> >>UPDATE RentalProperties >>SET RentalFee = @NewRentalFee >>WHERE RentalPropertID = 1 AND >>RentalFee = @OldRentalFee >>IF @@ROWCOUNT = 0 >>BEGIN >> RAISERROR('Data was updated by another user', 16, 1) >>END >>--the above raises an error >> >>A common practice to use a rowversion data type (formally timestamp) for the >>concurrency check. This simplifies concurrency checking because the value >>is automatically changed by SQL Server whenever any row value changes and >>you don't need special handling of NULLs. >> >> >>-- >>Hope this helps. >> >>Dan Guzman >>SQL Server MVP >> >>"Daniel" <Dan***@discussions.microsoft.com> wrote in message >>news:E6002470-2152-41AC-AE2F-D33A1676CCFA@microsoft.com... >> >> >>>Hi, >>>Does any one know how to apply/enhance the database's integrity. >>> >>>During study, i learn the theory of how to make sure the data retrieve and >>>update is correct. But i dont know how to apply in database development. >>>For example: I have a table. 2 persons are accessing the table in the >>>following sequence: >>>Person A, access the table. >>>Person B, access the table. >>>Person A update new value (such as rental_fee) >>>Person B also update the rental_fee value. >>>Person A exit >>>Person B also exit >>>At this moment, record updated by person A has been overwrited by person >>>B. >>> >>>How to prevent it? >>> >>>Thanks in advance. >>> >>>Best regards, >>>GL >>> >>> >> >> >> > I have another question, do i need to configure the sqlserver 2000 in There's no special SQL Server configuration needed since optimistic > order > to do that? concurrency is handled by the application. > In addition, creating a column (auto generate number) act as one of the This subject is often discussed in this forum so you can fund many pros and > primary key is a good practice? cons with a google search. It's a common practice to use a surrogate value, such as an IDENTITY column, as the primary key. Whether or not this practice is good depends on the specific situation. There are those who believe religiously that one should always use natural keys and others who always employ surrogate keys as a knee-jerk reaction. Personally, I evaluate each situation individually. -- Hope this helps. Dan Guzman SQL Server MVP ok. i got it..thanks for ur respond.
Show quote "Dan Guzman" wrote: > > I have another question, do i need to configure the sqlserver 2000 in > > order > > to do that? > > There's no special SQL Server configuration needed since optimistic > concurrency is handled by the application. > > > In addition, creating a column (auto generate number) act as one of the > > primary key is a good practice? > > This subject is often discussed in this forum so you can fund many pros and > cons with a google search. It's a common practice to use a surrogate value, > such as an IDENTITY column, as the primary key. Whether or not this > practice is good depends on the specific situation. There are those who > believe religiously that one should always use natural keys and others who > always employ surrogate keys as a knee-jerk reaction. Personally, I > evaluate each situation individually. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > >  |
|||||||||||||||||||||||
Other interesting topics