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 !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.|||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.|||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.|||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 youre booking seats on an aircraft you must ensure no other user books your particular seat between you reading that its free and you writing that youre 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 users 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 users writes. You cannot write through any other users 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.|||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