Usage Notes - Advanced SQL Engine - Teradata Database

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
rin1593638965306.ditamap
dita:ditavalPath
rin1593638965306.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantageā„¢

Use this function for a read/write GLOP that is shared by more than one external routine at the same time. Because it is not possible to determine whether a GLOP is being shared, the best practice is to use locks at all times. The only time not to use them is when the design of the data does not require a lock. For example, a design that divides the data in such a manner that ensures that all external routines access their own specific address range such that there is no possibility that another external routine can use the data at the same time can get by without using locks. (This scenario is highly unlikely.) For example, if the GLOP data gets updated in memory specifically at 12 AM everyday, all external routines can ignore placing read locks if the time is not close to 12 AM. In any case, any usage design needs to consider this issue.

Multiple read locks are granted and only one write lock is granted. All lock requests have a 130 second timeout associated with them. If the lock request cannot be granted within 130 seconds the function returns a value of -2. The caller must check for that and reapply the request or move on to do something else. The reason for doing this is to avoid any permanent deadlock. The external routine can always decide to abort or try again. Also, any granted lock has a timeout of 2 minutes. This default can be changed with a configuration parameter. If the GLOP is locked for more than two minutes , the system aborts the transaction. If the external routine terminates with a lock in place, the system aborts the transaction. This is done to avoid external routine code from locking the memory for extended time periods. It avoids having the database add complex code to try and solve global deadlock issues. Having the timeout also encourages efficient usage of the GLOP while locked. The lock command is only relevant to read/write data. There is no point in locking read-only data and it is not allowed.

This is an honor system. The external routine can change memory (if it is read/write) at any time with no locks. It can also read the memory with no locks. However the external routine must know whether doing that makes sense and could cause problems for other external routines.

Do not assume that because a read/write GLOP is mapped for example at the request level that locks are not needed, because that is not necessarily the case when there are parallel steps in the request. It still requires a lock to avoid any conflict.