Deadlock

[sadowsky]

One of our customers has the problem, that his database has a daily deadlock, whereas other customers - some of which have much larger data volumes - do not experience this problem.
This is strange, as the data connections for all cursors to our data base are done with ReleaseLock.
After turning on a protocol for the deadlock, I received the Dump that I am attaching below. But this dump doesn't help me to solve the problem.
Could you please help?
Thank you!


008,16-NOV-11 10:10:00.70,DEADLOCK database 'VERLAGE' contributing processes:
> Process 12.7: "
SELECT mwst_proz, SUM(mwst_betr), SUM(preis_pos), SUM(mwst_betr_fremd), SUM..." waits for 190282.455 (wants TSlock)
>> has TS lock on 190282.429
>> has S lock on 186938.186938
>> has S lock on database
> Process 20.15: " Insert Into SYSADM.AUF_POSI ( SYSADM.AUF_POSI.BEARB_KZ,SYSADM.AUF_POSI.MANDA..." waits for 190282.429 (wants TXlock)
>> has TX lock on 190282.268
>> has TX lock on 190282.323
>> has TU lock on 190282.429
>> has X lock on 186935.4394
>> has S lock on 3878040.16
>> has S lock on 3878040.4
>> has S lock on 3877861.9
>> has S lock on 3877861.7
>> has S lock on 3892675.76
>> has S lock on 255429.1
>> has S lock on 252952.96
>> has S lock on 43759.4
>> has X lock on 190890.131
>> has X lock on 190752.203
>> has X lock on 190571.239
>> has X lock on 190282.455
>> has X lock on 186935.4393
>> has X lock on 186935.3953
>> has I lock on 186935.3
>> has S lock on 186938.186938
>> has S lock on database
End of locks for database 'VERLAGE'
008,16-NOV-11 10:10:00.71,DEADLOCK,VERLAGE,MANON,ML350,RL,1000,190282,455,Temp S-Lock,

SELECT mwst_proz, SUM(mwst_betr), SUM(preis_pos), SUM(mwst_betr_fremd), SUM(preis_pos_fremd), SUM(provi_betr), SUM(provi_betr2), SUM(provi_betr3), SUM(rabatt_betr), SUM(gewicht_ges)
FROM sysadm.auf_posi
Where mandant = :1 and auftrags_nr = :2 and ( @nullvalue(verkettung_nr,0) = 0 or @nullvalue(verkettung,'0') = '1') GROUP BY mwst_proz

[Mike Vandine]

Can you please post your sql.ini file?

This is actually a classic deadlock.

> Process 12.7: "
SELECT mwst_proz, SUM(mwst_betr), SUM(preis_pos), SUM(mwst_betr_fremd), SUM..." waits for 190282.455 (wants TSlock)
>> has TS lock on 190282.429

> Process 20.15: " Insert Into SYSADM.AUF_POSI ( SYSADM.AUF_POSI.BEARB_KZ,SYSADM.AUF_POSI.MANDA..." waits for 190282.429 (wants TXlock)
>> has X lock on 190282.455

So, the first process is waiting to put a TS lock on page 190282.455, which process 2 already has an X lock on and the second process is waiting for as TX lock on 190282.429 which process 1 holds.

Neither can get a lock until one releases. Deadlock! One of the processes loses and gets rolled back.

[FRBhote]

With 11.x, I have many deadlocks which are meaningless - have learnt to live with them.

008,16-NOV-11 17:10:33.09,DEADLOCK,DVNAME,xxx,xxx,RL,30,5424436,77,S-Lock,
SELECT CO_CODE, CO_NAME FROM SYSADM.SCRIPS WHERE CO_NSE_1 = :1

[ursamajor]

How is the diskperformance of the customers server?
If queries have a large cost, or are complex, they take longer to finish, so the chance on a deadlock is higher.

[RainerE]

Hi Feroz!

With 11.x, I have many deadlocks which are meaningless - have learnt to live with them.

SB 11.x was annouced to reduce (exspecially internal) dealocks (regarding to SB 9.x which we still use because we do not get a newer version running). Can't you confirm this?

In SB 9 we have deadlocks on simple selects (

Regards,
Rainer

[FRBhote]

Hi Rainer,

I could not use SB 9 at all, and all but one of my customers are on 8.5. 11 has not only meaningless deadlocks and timeouts but also crashes 2-3 times a day with no fail.sql. A very irritating habit with 70-80 users online.

Feroz

[sadowsky]

Hi Mike,

many thanks for your explanation.

This is the server sction of sql.ini :

[dbntsrv]
country=germany
ANSIJoinSyntax=1
centurydefaultmode=1
servername=verlag
autolocktable=0
batchpriority=0
cache=256000
dbdir=G:\SQLBase
readonly=0
DBNAME=TEST,SQLWS32

could it be an option if we change the isolation level on RC?

Regards,
Volker

[Mike Vandine]

I wouldn't recommend using RC in 11.5. 11.6 SP2 has some fixes that should actually make this usable now. In fact, there are settings in the sql.ini file that will allow you to test out the various RC levels *without* even changing your code by automatically changing an RL connection to an RC with the default option that you also specify. So, you can change all RLs to RC1, for example to see the affect that running in RC1 will give you.

[Mirko]

Hi Mike,

Where can I find documentation on RC1 (is there a rc2 and 3 ?) ? Wich version vas introduced in ? Do you think it's a good idea to just change Isolation Level on a big application and just see how it goes ? The application was build with a RL aproach ! Would this be correct for RC1 ?
Users say that since 11.6 they experience more and more deadloks ! Well I do not belive everything a user says but, as a matter of fact, it appears to be the truth

TIA
Mirko

[Mike Vandine]

Have a look at the attached, a presentation that I did at the DevCon in 2010. This is relevant to the 11.6 and it (finally) works as it should with SP2 of that release.

Look closely at the autorc and defaultrc settings for the sql.ini file. The autorc will allow you to automatically change any RL connections to RC so you don't need to actually change your code to be RC in order to test out the affects of changing to RC.

[Mirko]

Thanks Mike,

I did attend YourPresentantion@DevCon.2010

Do I have to change autorc on the server .ini or on the clients ones ???

Am I supposed to play around with defaultrc too ?

TIA
Mirko

BTW Am I blind or this information is missing on the Documentation shipped with sb11.6 sp2 ???

[Mike Vandine]

AUTORC is only needed for the server. I would set the defaultRC to whichever is necessary to get the timestamp set correctly for your application. But if you are using the autorc, personally I would make sure that I've got the right one set.

The nice thing is that you can try the different defaults to see which is most appropriate for you.

[Mirko]

Hi Mike,
My concerns are about the clients ! They are all developped in td42 and the connectivity dll are the ones installed with deploy42 (sqlwntm.dll => 9.0.1.13765) !!!
Do you think it's a good idea to test it in such an envirenement ???
Should I deploy newer connectivity dlls before ?

TIA
Mirko

[Mike Vandine]

Hi Mirko,

The client DLLs should be OK (it says here in fine print ). I would certainly try this on a local testing system before giving it to your customer, though.

[Mirko]

As far as I can read in the documentation the readonly param in the server sql.ini MUST be set to TRUE !
Is this still correct with 11.6, Mike ?
TIA
Mirko