Understanding locking on tables

Database
Teradata Employee

Understanding locking on tables

Hi,
I have one table called ABC which is use in the following three scripts as:

Script_A
INSERT INTO ABC
SELECT * FROM XYZ;

Script_B
UPDATE ABC SET LOAD_FLAG = '1';
INSERT INTO **bleep**
SELECT * FROM WHERE ABC LOAD_FLAG = '1';

Script_C
DELETE FROM ABC WHERE ABC LOAD_FLAG = '1';

My question is that if all three scripts run in parallel, which DML operation will block the other? I read that INSERT takes table level lock which means that if Script_A is running, none of the other two can run. Will it be same if UPDATE in Script_B is running and any of the other two scripts trigger?

And in case of SELECT present in Script_B, will it run if Script_A is running?

Please note that LOAD_FLAG column is not a part of any index.

4 REPLIES 4
Senior Apprentice

Re: Understanding locking on tables

Hi,

 

Which DML runs first will be the one that is received and parsed first (ignoring any TASM rules which may put it on to a delay queue etc.). Looking at your three scripts:

 

Script_A
INSERT INTO ABC
SELECT * FROM XYZ;
This will place a table-level WRITE lock.


Script_B
UPDATE ABC SET LOAD_FLAG = '1';
INSERT INTO **bleep**
SELECT * FROM  ABC WHERE LOAD_FLAG = '1';
The update will place a table-level WRITE lock on ABC. The SELECT will place a table-level READ lock on ABC.(Note that I've changed the SELECT to be what I think it is meant to be - swapped the ABC and WHERE words around).


Script_C
DELETE FROM ABC WHERE ABC LOAD_FLAG = '1';

Because LOAD_FLAG is not indexed, this statement will place a table-level WRITE lock.

 

So all four statements shown above require a table-level lock, and because you have incompatible locks only one of them can run at a time. Whichever one gets to place that lock first will be the one that runs first.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Understanding locking on tables

Thanks Dave for a very detailed and nice explanation.

 

Is there any way to avoid this blockage. Script B and C will always run in sequence but A can come in parallel to either B or C and we want to execute that in parallel.

Senior Apprentice

Re: Understanding locking on tables

No, I don't think you can - certainly not with the current code - (remember that locking is there for a reason, it protects your data, helps to prevent data 'corruption').

 

You'd have to change table design and code to be able to use rowhash locks and/or partition locks. That will typically be a lot of work.

 

How long do these statements take - how long are they actually waiting?

 

Are you sure you want to be inserting rows into a table whilst another process is deleting/updating rows?

How do you ensure that the just inserted rows are not then deleted?

 

This is why locking is implemented.

 

Sorry, but I don't think you can do what you want.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Highlighted
Teradata Employee

Re: Understanding locking on tables

Hi Dave,
Thanks for your response.

Actually, this is a new design, so we are not sure how long will it take.

The idea is that the table ABC will keep on loading from the source files 24 hours a day, whenever there will a new file available in the landing area. Whereas the UPDATE, SELECT and DELETE on the table ABC works during the ETL loading which will work only during the ETL loading window. So probability of this blockage is not that much.

Regarding your question "How do you ensure that the just inserted rows are not then deleted", this is actually done by the LOAD_FLG which will ensure that before processing the records, it will first UPDATE the LOAD_FLG to 1 and then consume those rows and then DELETE those rows where LOAD_FLG is 1. I forgot to mention that in SCRIPT_A where we are inserting in ABC, the LOAD_FLG will be set to 0 which shows that these are new rows and not loaded into warehouse yet.