Toad World® Forums

TPC-E benchmark test failing on deadlocks


#1

hi,

I have been trying to to the TPC-E benchmark test for ASE 15.7 on RHEL 6.5. The process of creating tables etc. goes fine and almost everytime it fails after scale check on deadlocks. So it is not even like halfway. I have selected single user load of 25, 50 and 100 and tables creation option of (create/load objects if objects don’t exist)

Started test TPC-E Transaction Mix

Checking scale factor…

Scale factor check complete.

Agent(MYDESKTOP) Error: CT-LIBRARY Server Message #1205 - Your server command (family id #0, process id #221) encountered a deadlock situation. Please re-run your command.

Transaction “TradeOrder Transaction” run failed by virtual user 22 in agent 1 of MYDESKTOP

Test cancelled due to errors.

Any suggestions? or any settings I am missing out?


#2

It looks like the deadlock is occurring after the scale factor check but you can disable the check by going to Edit - Settings and then select Benchmarks on the left hand side and uncheck the “Check scale factor before running benchmark test” checkbox.

Now to troubleshoot the deadlock error. Look at the Agent error log file on MYDESKTOP for additional error information. Also look in the Result Logs directory on the console machine (located in the /My Benchmark Factory//) for the log file for when the objects where created to make sure everything, especially the indexes, created successfully.

Also what scale factor are you running at for the TPC-E?


#3

hi Kevin,

Thanks for the suggestions. I have actually enabled deadlock printing on the ASE server to capture the details. Don’t think it’s the scale check… that I saw in the messages. The deadlock happens with the transactions. Insert and Update on E_TRADE table by two processes. I have the text below… The ASE is by default creating ALLPAGES lock tables. I am going to change the lock to DATAROWS and see if it will help

By the way, am using default scale of 1. I saw it should be 500 somewhere in the guide. But I do not have the luxury of that much database space for testing! :slight_smile: Even scale of 1 creates tables almost 10 GB in size. Any recommendations here? Any ratio between user load and scale for TPC-E?

Deadlock Id 4 detected. 1 deadlock chain(s) involved.

Deadlock Id 4: Process (Familyid 0, Spid 283, Suid 1) was executing a INSERT command in the procedure ‘objid 423643407’ at line 1 at nested level 1.

Deadlock Id 4: Process 283 was involved in application ‘Benchmark Factory’.

Deadlock Id 4: Process 283 was involved on host name ‘M’.

Deadlock Id 4: Process 283 was involved in transaction ‘$user_transaction’.

SQL Text: Insert Into E_TRADE (T_ID, T_DTS, T_ST_ID, T_TT_ID, T_IS_CASH, T_S_SYMB, T_QTY, T_BID_PRICE, T_CA_ID, T_EXEC_NAME, T_TRADE_PRICE, T_CHRG, T_COMM, T_TAX, T_LIFO)Values(17280014, GetDate(), ‘SBMT’, ‘TMB’, ‘1’, ‘AGA’, 800, @bid_price, 381, ‘An White’, NULL, @charge, @comm, 0, ‘’)If @type_is_market = ‘Y’ Insert Into E_TRADE_REQUEST (TR_T_ID, TR_TT_ID, TR_S_SYMB, TR_QTY, TR_BID_PRICE, TR_B_ID) Values(17280014, ‘TMB’, ‘AGA’, 800, @trade_price, 0)Insert Into E_TRADE_HISTORY (TH_T_ID, TH_DTS, TH_ST_ID)

Executing procedure: ss0423643407_0606214659ss

Subordinate SQL Text:

Insert Into E_TRADE (T_ID, T_DTS, T_ST_ID, T_TT_ID, T_IS_CASH, T_S_SYMB, T_QTY, T_BID_PRICE, T_CA_ID, T_EXEC_NAME, T_TRADE_PRICE, T_CHRG, T_COMM, T_TAX, T_LIFO)Values(@@@V0_INT, GetDate(), @@@V1_VCHAR1, @@@V2_VCHAR1, @@@V3_VCHAR1, @@@V4_VCHAR1, @@@V5_INT, @bid_price, @@@V6_INT, @@@V7_VCHAR1, NULL, @charge, @comm, @@@V8_INT, @@@V9_VCHAR1)

Deadlock Id 4: Process (Familyid 0, Spid 260, Suid 1) was executing a UPDATE command in the procedure ‘objid 471643578’ at line 1 at nested level 1.

Deadlock Id 4: Process 260 was involved in application ‘Benchmark Factory’.

Deadlock Id 4: Process 260 was involved on host name ‘M’.

Deadlock Id 4: Process 260 was involved in transaction ‘$user_transaction’.

SQL Text: Update E_TRADE Set T_COMM = @comm_amt, T_DTS = GetDate(), T_ST_ID = ‘CMPT’, T_TRADE_PRICE = @trade_price Where T_ID = 17280029 Insert Into E_TRADE_HISTORY(TH_T_ID, TH_DTS, TH_ST_ID) Values(17280029, GetDate(), ‘CMPT’) Update E_BROKER Set B_COMM_TOTAL = B_COMM_TOTAL + @comm_amt, B_NUM_TRADES = B_NUM_TRADES + 1 Where B_ID = 0 /

Executing procedure: ss0471643578_1735357783ss

Subordinate SQL Text:

Update E_TRADE Set T_COMM = @comm_amt, T_DTS = GetDate(), T_ST_ID = @@@V0_VCHAR1, T_TRADE_PRICE = @trade_price Where T_ID = @@@V1_INT

Deadlock Id 4: Process (Familyid 0, Spid 260) was waiting for a ‘update page’ lock on page 38156930 of table ‘E_TRADE’ in database ‘unity_test’ but process (Familyid 0, Spid 283) already held a ‘exclusive page’ lock on it.

Deadlock Id 4: Process (Familyid 0, Spid 283) was waiting for a ‘exclusive page’ lock on page 41654325 of table ‘E_TRADE’ , indid 5 in database ‘unity_test’ but process (Familyid 0, Spid 260) already held a ‘update page’ lock on it.

Deadlock Id 4: Process (Familyid 0, Spid 283) was chosen as the victim.

Victim process host = M', user =sa’ program name = Benchmark Factory' host processes =5568’ .

End of deadlock information.