Results 1 to 6 of 6

Thread: Help:Locking...

  1. #1
    Join Date
    Jan 2004
    Posts
    11

    Unanswered: Help:Locking...

    Hello .
    I am using SQL Server 2000 in order to create a multi user program that accesses data.
    The problem is that multiple users will update and select data at the same time at the same table.

    Is there a way to avoid deadlocks ?
    I heard about two ways: using a temporary table to store data and then write the data only when the user finished the update.
    and the other is using xml to write the database to a xml file that is stored locally. do the updates on the file and then after completion insert the xml file into the database.

    does anybody know much about these ways? do you know where i can find code for this ?

    is there a better way?

    thanks !
    and happy new year !

  2. #2
    Join Date
    Jan 2004
    Posts
    1

    Re: Help:Locking...

    yeah, according to the textbook of database, there should be 2 other ways instead of write the information to other place from the database.

    1. lock all what u will lock in the transaction when u begin the transaction.

    2. all kinds of resources should be locked at the same order.

    hehe.

    happy new year.

  3. #3
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Help:Locking...

    Those two ways you described will help but truely it all depends on how you write your sql script. Are all the updates and deletes using cursors? Do you have begin trans and commit on all your transactions? There is no one way you can completely avoid deadlocks, but there are ways to manage it. The best way is to let us know what sql script in your environment that creates deadlock so we can better help with your situations.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Are we talking about deadlocks or blocking here? It surely sounds that the poster is concerned about object availability when more than one connection attempts to access its data.

  5. #5
    Join Date
    Nov 2003
    Posts
    94
    There is no silver bullet to avoid deadlocking other than fully analysing the transactions you intend to commit. Using a temporary table may work, as long as your transaction does not depend upon data previously read from the permanent tables remaining unchanged during the accretion of the temporary data. XML in this regard is a complete red herring.

    Essentially you control locking policy using the SET TRANSACTION ISOLATION LEVEL command and wrapping all calls with an outer transaction until all components of the unit of work are available for committing.

    You need to consider whether the integrity of data reads is essential to the integrity of the intended data writes. If you’re booking seats on an aircraft you must ensure no other user books your particular seat between you reading that it’s free and you writing that you’re booking it. To make reads fully transactional with writes you use isolation level SERIALIZABLE – everything you read is locked against being updated or inserted against for the duration of the transaction. Isolation level REPEATABLE READ ensures that data you have read cannot be updated by another user, but allows background inserts.

    If it is not possible to extend the duration of the transaction between critical reads (i.e. you can keep your option on the flight seat open for twenty minutes) you must implement soft locking of the seat row in the permanent tables using something like a timestamp or a flag.

    Isolation level READ COMMITTED ensures you can only read data that has had full transactional commit to the database. You use this read mode to ensure you never read over another user’s half finished work, but you have no locks of the data you have read for the purposes of subsequent writes. READ UNCOMMITTED means you read through any locks applied during any other user’s writes. You cannot write through any other user’s locks.

    Particularly useful in ensuring transactional integrity is SET XACT_ABORT ON, which will cause any error anywhere in your SQL to Rollback the entire transaction.

    Also be aware that if a deadlock does occur SQL will terminate one or other transaction, at random, unless you explicitly set a deadlock priority on the thread whose death you would prefer to occur.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, use strored procedures and stroe the data locally...

    don't do dynamic sql

    don't open recordsets....

    Read data store

    manipulate data in the app

    when an action is to occur...update or delete, check the records timestamp to see if someone else alread modified the record and act accordingly

    if ok, exec (through a sproc) your transaction...

    keep'em short
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •