Archives of the TeradataForum

Message Posted: Tue, 21 Jul 2009 @ 16:27:03 GMT

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

The risk with concurrent updates is different sessions blocking on the rowhash lock including potential deadlock.

TPump typically uses multiple sessions and on each session packs >1 dml statement (insert/upsert) into the request.

If there are nupis or hash collisions (different PI but same hash) then 2 sessions locking the same rowhash can block. A common case for hash collisions is a char/varchar pi with numeric values--that doesn't hash well.

Also JIs and SIs.

Within a job for NUPIs TPump has a 'serialize' option to send all dmls affecting the same nupi values on the same session. This does not help with hash collisions though.

Before using tpump against some table it is recommended to see if this is the case--do select count(pivalue) select count(distinct(pivalue)) and select count(distinct(hashrow(pivalue))) and if the count of distinct hashrows is much less than the count that indicates risk of blocking

With >1 session you have a risk of blocking in those cases.

Sometimes multiple jobs are naturally partitioned e.g. if the store number were part of the PI and different TPump's processed data from different stores.

As to access, the typical recommendation is that the queries use 'locking for access' to avoid blocking with the inserts.

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