Archives of the TeradataForum

Message Posted: Fri, 01 Jan 2010 @ 09:04:10 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hello All,

The DBA team at our organisation have been facing this issue for quite some time. We have a near real time D/W on which several(thousands and thousands of) "tactical-PI-based-DML" operations run at any time of the day. This has been achieved using multistatement request(MSR) mechanism.

To simplify the problem further, we have the following 2 sample queries(updating a different column of the row(s) with the same PI value) running at exactly the same time as part of 2 different concurrent requests.

Query 1(as part of a MSR: MSR#1):

     Update 
     set ='ANY VALUE'
     where = VAL1

Query 2(as part of another MSR: MSR#2)

     Update 
     set ='ANY VALUE'
     where = VAL1

Please note that the table: tbl1 has a NUPI. MSR#1 and MSR#2 cannot be done sequentially(one after the another). Business requirement says that both these MSR's satisfy a different business function that cannot be combined together.

Often it happens that these two queries get stuck in a row hash deadlock mode and one of them aborts(say query#1) and the other succeeds(say query#2). The table (on which deadlock occurred), sometimes gets caught in the locking logger and sometimes does not.

So I have the following questions for you all:

1. Is there any way to minimize row hash deadlocking?( Please do not suggest using TPUMP/SERIALIZE as this will require a significant architechture change. Also the real time D/W in our environment required transaction capability where if one statement fails the entire transaction must be rolled back.)

2. Despite, getting all other lock information that happened at the same time, sometimes locking logger does not show data for the table on which RH deadlock occured. Why so? What precise steps can be taken to find this table then? If there is no data found in the locking logger, it becomes very difficult for the DBA team to find the successful query (query#2) out of 1000's of them that ran at the same time.

Any sort of help in any particular direction will surely help. Thank you all in advance.

Regards,

Suhail

	<Prev	Next>		<<First	<Prev	Next>	Last>>