Re: Update a record in SQL Database from C#.NET

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Mihaly (Mihaly_at_discussions.microsoft.com)
Date: 12/31/04 

Date: Fri, 31 Dec 2004 01:59:01 -0800

   I thinked to this solution, but I tried an other. When the user begin the
update, I begin a transaction with isolation level read committed and read
the record. Then the user change the record's date and saves the changees.
When the user saves the new values I do the update and the commit transaction.
   In this case I can read the record which was not updated and I can update
the record after the first user updated it.
   If I use the isolation level serializable, I get the good solution, but
if the user which was read first the record is too slow, he blocks the other
users.
   The best solution is with timestamp? In this case I need a timestamp
field in any table.

   Thank you for help and I wish you a Happy New Year!
   Mihaly

"Ken Allen" wrote:

> SQ Server supports a data type called TIMESTAMP that can be used
> specifically for this purpose. You do not have to specify a value for
> fields that are of this type; the database will automatically assign a
> value each time a record is created or updated.
>
> Ensure that you retrieve this column with your query, and ensure that
> you include it in the WHERE clause of the update. You can use this
> approach whether you are building your own UPDATE statement or if you
> are using the Update() method on the DataAdapter object.
>
> Note that you should always check the return value from the execution of
> the UPDATE statement or the Update() method call to ensure that the
> proper number of records have been modified. If the record was changed,
> then the count will be zero; leave this out and a malformed request may
> update more than one record. For that reason it is often best to wrap
> these updates in a transaction so that you can roll back the attempt in
> case of a detected error.
>
> -Ken

Relevant Pages

  • Re: controlling lock order in transactions
    ... I believe the default isolation level for .net may be ... My transaction ONLY performs INSERTS. ... Yes it will put an exclusive lock on the row being inserted but Serializable ... COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)
  • Re: counting rows
    ... e.g. in read commited isolation level with two transactions ... At the start of the transaction they do (sql 2005 has versioning now also ... you'd need to serialise in order to make sure you didn't have ... s2: commit; ...
    (comp.databases.theory)
  • Re: restore transaction isolation level
    ... Fatal error doesn't go through the trap ... > You can set the isolation level back to READ COMMITTED: ... > SET TRANSACTION ISOLATION SERIALIZABLE ... >> COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)
  • Re: restore transaction isolation level
    ... Fatal error doesn't go through the trap ... > You can set the isolation level back to READ COMMITTED: ... > SET TRANSACTION ISOLATION SERIALIZABLE ... >> COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.server)
  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... the file then that inode would be added to the journal list and the pages ...
    (Linux-Kernel)