Deadlock

[Skim]

Dear Reader!

We are using a MSSQL 2000, Delphi 7, W2K Server

We are running several concurrent processes on this server and hundreds of
clients are connected and permantly updating there information on the
screen. They are all connect to this
MS SQL 2000 Server via ADO.

From time to time we get deadlocks.

Does it help setting readonly tables to adLockReadonly?
or does adLockOptimistic the same if I only call "SELECT ..."?

It seems that a deadlock also happens, when I just want to
read data.

Thanks for help, SKIM

[Michael Rodriguez]

"Skim" <sk...@gmx.net> wrote in message
news:40a47a9e$1...@newsgroups.borland.com...

> Dear Reader!
>
> We are using a MSSQL 2000, Delphi 7, W2K Server
>
> We are running several concurrent processes on this server and hundreds of
> clients are connected and permantly updating there information on the
> screen. They are all connect to this
> MS SQL 2000 Server via ADO.
>
> From time to time we get deadlocks.
>

Skim,

I had the exact same problem. Here are some things you could try:

SELECT * FROM table1 WITH (NOLOCK)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (<-- only for non-critical
reads)
SET IMPLICIT_TRANSACTIONS OFF

The hardest problem to track down was the implicit transactions issue. If
you run traces with Profiler you'll discover instances where transactions
were started without you telling it to be in a transaction. The killer here
is if that happens while a dialog box is waiting on user input. You also
have to be careful with the implicit transactions because I found that
Delphi kept turning them back on for me. I eventually set the TAdoDatabase
component to run the set statements again after every transaction, committed
or rolled back. For all of my posting procedures and important reports, I
just set the isolation level back to read committed. You'll have to weigh
the possibly of reading certain kinds of uncommitted data versus the
database lockups and decide for yourself which is the bigger issue.

Good luck!

Mike Rodriguez

[Brian Bushay TeamB]

The first thing I would do it look over your code to make sure there are no
transactions active while the user has control of the application.
--
Brian Bushay (TeamB)
Bbu...@NMPLS.com

[Skim]

If I do a simple "Select * from table". Does an ADOQuery
lock the rows while reading?

Most of the clients only read information. And I left the software
running for hours and suddenly I had an deadlock error
(transaction ID=xx deadlocked, process killed)

I understand that updates are critical, but this error is very strange...

Thanks for help again.

"Brian Bushay TeamB" <BBu...@Nmpls.com> schrieb im Newsbeitrag
news:66raa0dj3epmc2edt...@4ax.com...

[Brian Bushay TeamB]

>If I do a simple "Select * from table". Does an ADOQuery
>lock the rows while reading?

ADO query just passes the select through to your database.
SQL server uses a Share lock when reading rows but this won't cause a deadlock

>Most of the clients only read information. And I left the software
>running for hours and suddenly I had an deadlock error
>(transaction ID=xx deadlocked, process killed)
>
>I understand that updates are critical, but this error is very strange...

You don't have to have updates to cause a deadlock if you have transactions that
are held open during user interaction.

You might also want to look at the topic "minimizing deadlocks" in SQL books
online

[Skim]

Thanks a lot for help.

I read a lot in the SQL help, but I can't get a clue to it.

Is a table or row locked, while executing a "select" statement?
I used "readcommited" as discribed in the the SQL 2000 help.

Can I force the server to "dirty" read the information, without lock?

Thanks again.

[Del Murray]

That question cant be answered unless one know more about your application
design. AFAIK, just "select * from table 1" does not do a lock of any kind
unless you force it to. Why would it. Insert, update etc cause locks. Insert
in general will lock table, update will lock page unless the select
statement tell him to only lock row. UpdateBatch from ado can get you
sometimes if you have lots of records in the dataset that have been updated
and are being now committed. An "implicit" transaction is started. Row 1
might lock a different page from Row 2. If the data is all over the place in
the database, then many many pages from the database can be locked while the
transaction is active. If the application then has a dialogue box that say
.. "records updated , are you sure you want to commit these" (which is
stupid but sometimes people do it) and the use is at luch because the update
query is poorly written and takes 100 times longer than it should, then the
answer is not returned, the transaction stays active, the locks stay locked
and deadlocks have a higher chance of occuring.

I have debugged some apps where there were stored procs calling udfs that
returned tables as variable and the tables were created with locks on the
rows and I had to dig so deep to find the problem. Check for stuff like
that. The lock may not be surfaced where you can easily see it.

Building temp tables all over your app can cause a problem cuz that causes
table locks on tempdb and when you have 100 users creating temptables at the
same time it can cause havoc with the db.

there are some memory issues with the number of locks that can be processed
and you might want to poke around in BOL to see what setting affect that
also.

HTH

[Michael Rodriguez]

> Is a table or row locked, while executing a "select" statement?

Yes. A shared lock is placed on the table. While this is not normally a
problem, it will prevent any other users from obtaining an exclusive lock on
the same table. That requirement is rare, but it can and does happen. If
you want to ensure that your select statement doesn't block anyone, just use
a "WITH (NOLOCK)" in the FROM clause for each table.

> Can I force the server to "dirty" read the information, without lock?

Those are two separate issues. You can allow dirty reads by setting the
transaction isolation level to read uncommitted. This should be used with
extreme caution and only for non-critical data. If you don't want your read
to place any locks, again, just use the with (nolock) clause.

HTH,

Mike Rodriguez

"Skim" <sk...@gmx.net> wrote in message

news:40a8...@newsgroups.borland.com...

[Kevin Frevert]

Skim,

Without knowing your database/table structure and your application design,
it would be impossible to give a specific answer.

(in no particular order)
1. Make sure your database/tables are normalized
2. Make sure your tables are optimized (indexed properly, have a maintenance
plan, etc)
3. Make sure you are as specific as possible in your client applications.
Avoid having 'Select * From '... with no where clause.

Here are some good articles to check out..
http://bdn.borland.com/article/0,1410,28160,00.html
http://www.hower.org/Kudzu/Articles/DBDesign/index.html
http://bdn.borland.com/article/0,1410,27790,00.html

Good luck,
krf

"Skim" <sk...@gmx.net> wrote in message

news:40a47a9e$1...@newsgroups.borland.com...

[skim]

Thanks a lot for all the answers!

Skim

"Skim" <sk...@gmx.net> schrieb im Newsbeitrag
news:40a47a9e$1...@newsgroups.borland.com...