DeadLock Issue

I serveral triggers in a table that is accessed by mutilple users in the application I am writing. I have come across a deadlock issue and have tried to resolve the issue by breaking down the triggers into many much smaller trans with no success. In general terms, can some one suggest some technique I am missing that I can try to avoid this issue .There are 3 techniques that can be used to help you avoid deadlocks. Number one is to ensure the same order of access to objects within a transaction. This comes from the definition of the deadlock itself:

TableA has a lock placed by user1 who's trying to access TableB,while user2 has a lock on TableB and trying to access tableA.

To break this vicious circle you need to structure all transactions in such a way that TableA is always accessed first.

Number two is to make your transactions as short as possible, without violation business logic and business requirements of course.

Number three - and here there will be a lot of screaming, yelling, calling names, - but reality will prove them all wrong,- lower transaction isolation level.|||rdjabarov and I disagree on that last point. I keep insisting on correct answers, he prefers easy answers.

In reality, if what you need is a quick answer the rand() function will often get you there orders of magnitude faster than you can possibly get the correct answer, with no locking issues at all.

-PatP|||Number three - and here there will be a lot of screaming, yelling, calling names, - but reality will prove them all wrong,- lower transaction isolation level.

When the users are screaming that: WE CAN'T COMPLETE OUR WORK, IT'S TOO SLOW AND WHATE THE HECK IS THIS timeout-expired IN OUR APPLICATION?
I don't see anything rising in the PERFMON at my dbserver but LOCKREQUEST/SEC (constantly above thousand figures) and high processor queue length due to blocking.
What now!
Same is the case when there's deadlock error, sp_lock contains more than hundereds of rows.
I'm just venting and cryin for the "Number Three advice"

One more thing that's rather strange and confusing:
why isn't there any READPAST isolation level. I guess it's by default in ORACLE.
[user1]
BEGIN TRAN
UPDATE mytable SET a=b
..
[user2]
select * from mytable
all previously committed rows r returned.

But in SQL, user2 waits :confused: , rather have to put select * from with (readpast) from mytable.
I guess [readpast] is better than [nolock]

Howdy! ;)|||OK, Pat, it's all circumstantial, let's agree at least on something (otherwise one of us will have to choose a different forum, and that's not what I intend to do, nor do I encourage for you to consider). When my app references Countries+States+Zips tables while displaying information to 100+ simalteneous users at the call center, don't you think it would be an overkill to use READ COMMITTED default isolation level for SQL? What are the chances that a call is placed to a person that lives in a country that has just been discovered, recorded in Countries table, associated States entries made, and their postal system delivered zip mapping to the call center, probably along with a list citizens that immediately received telephone service and became potential customers? It borders with absurdity, but that's what I feel when I see so much passion in your postings about NOLOCK.

EDITED: And how can RAND() help you?|||Setting connection level lock handling to allow permissive locking (lock handling like what NOLOCK gives) is one thing, to override the locking for a specific table within a transaction (actually using NOLOCK) is entirely different even though both situations affect the handling of locks.

Setting the transaction isolation level for the spid says that locking isn't important for this thread (spid) and is usually quite safe. You won't inadvertantly clobber important data due to a mangled or misprocessed read/write combination either within or between threads. This is a fine solution for the kind of problem you proposed where multiple users might need browse access to the same data.

Setting the lock handling for a specific table to a lower level than the thread that uses it requires a lot of knowledge about the entire system involved in order to have a prayer of being safe. I've had to do this before, but it isn't anything I'm comfortable with and wouldn't recommend for anyone that I didn't really, really hate. It takes ongoing monitoring to prevent a rogue process from upsetting the delicate balance that it depends upon.

I don't have any problem with setting the transaction isolation level, but am really, really wary of using NOLOCK explicitly. There are cases where it can be quite necessary... These are not the kind of solutions that I recommend for the long haul. They might fill a particular need under very specific circumstances, but they are dangerous.

-PatP|||And how about EXPLICITLY specifying READPAST in queries, ain't it better.?|||And how about EXPLICITLY specifying READPAST in queries, ain't it better.?I can't see any way that it is better from the standpoint of avoiding lost data... You can do as you wish, just be forewarned that I've done exactly what you are describing and really, REALLY regret it. Indiscriminantly ignoring locking is an easy solution, but like most easy solutions, it can be VERY dangerous.

If you set the transaction isolation level down, then everything in that transaction is processed at the lower level. I don't know of any way to hurt yourself that way. Setting some (even one) table to a lower locking level than the transaction isolation level is dangerous from the standpoint of "lost" data.

-PatP|||Pat, relax, there are more important things in life...like beer ;) Go get one, willya?!|||If you set the transaction isolation level down, then everything in that transaction is processed at the lower level. I don't know of any way to hurt yourself that way. Setting some (even one) table to a lower locking level than the transaction isolation level is dangerous from the standpoint of "lost" data.

No doubt the teacher is always right

and guru, i love you! :)|||Pat, relax, there are more important things in life...like beer ;) Go get one, willya?!No can do this week. I'm at training, so I'm always the designated driver. This week needs "Commando Driving 301" skill almost all the time in Chicago and the burbs. They've done some quite "creative" things on both 88 and 294, making getting from place to place a whole new experience!

Last night I actually made better time on Ogden (from 294 to Naperville Road!) than a friend did on I-88 !!!

-PatP

dbinit(), dblogin(), how often?

I'm a MS SQL newbie and am programming SQL using MS DS C++ 2003.

I'm writing sql code that will reside in a shared dll, used by many
processes and many threads in those processes.

So how often do I need to call dbinit()? Only the first time the DLL is
loaded, once per new process, once per thread, or once per database open?

Same question for dblogin().

Thanks very much for any help.
Bruce.Bruce. (noone@.nowhere.com) writes:

Quote:

Originally Posted by

I'm a MS SQL newbie and am programming SQL using MS DS C++ 2003.
>
I'm writing sql code that will reside in a shared dll, used by many
processes and many threads in those processes.
>
So how often do I need to call dbinit()? Only the first time the DLL is
loaded, once per new process, once per thread, or once per database open?
>
Same question for dblogin().

Zero times. At least unless you have some very special reason to use
DB-Library at all, like the need to support a legacy application. To wit,
DB-Library is a deprecated client API, and it lacks support for new features
added since SQL7, as Microsoft has not touched it for the last 8-10 years.

The recommended choice for a C++ application are ODBC and OLE DB. Of these
the ODBC is probably a lot easier to work with.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99425A6E7696FYazorman@.127.0.0.1...

Quote:

Originally Posted by

The recommended choice for a C++ application are ODBC and OLE DB. Of these
the ODBC is probably a lot easier to work with.

Not an option in this case but thanks for your reply anyway.

Bruce.|||Bruce. (noone@.nowhere.com) writes:

Quote:

Originally Posted by

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99425A6E7696FYazorman@.127.0.0.1...

Quote:

Originally Posted by

>The recommended choice for a C++ application are ODBC and OLE DB. Of
>these the ODBC is probably a lot easier to work with.

>
Not an option in this case but thanks for your reply anyway.

I'm sorry I was not able to answer your actual question at the time, but
I did not have access to some old source code that I have. Having looked
at that one, I see that I have this:

// Init DB-Library if we are the first player.
EnterCriticalSection(&CS);
if (no_of_threads++ == 0) {
if(dbinit() == FAIL) {
croak("Can't initialize dblibrary...");
}
// Set up the error handlers once for all.
dberrhandle(err_handler);
dbmsghandle(msg_handler);
}
LeaveCriticalSection(&CS);

// Set up LOGINREC struct for this thread.
td->login = dblogin();
DBSETLUSER(td->login, NULL);
DBSETLPWD(td->login, NULL);
DBSETLHOST(td->login, getenv("COMPUTERNAME"));

That is, call dbinit() when the DLL is initiated, but call dblogin once
for each thread. Then again, I guess the reason I did it this way was
to permit different threads to use the different login information. If
all threads will use the same login details, I can't see anything else
than that it would be sufficient to call dblogin() once, since LOGINREC
appears to only hold static data.

But permit me again to point the unsuitable in using DB-Library for new
development. Or to be more blunt: it's sheer silliness. If nothing else,
it's a waste of time for your professional development. The likelyhood
that you will get the oppurtunity to reuse the knowledge of DB-Library
programming are slim, whereas learning to master the ODBC API can be very
useful.

Why would ODBC or OLE DB not be an option in your case?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99436DC7B5A82Yazorman@.127.0.0.1...

Quote:

Originally Posted by

That is, call dbinit() when the DLL is initiated, but call dblogin once
for each thread. Then again, I guess the reason I did it this way was
to permit different threads to use the different login information. If
all threads will use the same login details, I can't see anything else
than that it would be sufficient to call dblogin() once, since LOGINREC
appears to only hold static data.

That's very interesting and helpful. Thanks for the information.

Bruce.