Skip to content

Archive for

ERROR: Concurrent update or delete of same row

ERROR: Concurrent update or delete of same row [tbl 212201676 dsid 36 tx 0x572822a U prev 0x572801c]

POSSIBLE ISSUE: You will possibly see this error in Netezaa if you try to update a row which is already deleted.

RESOLUTION: Do not try to update delete record.

Below is the example to support this answer:

TESTDB.ADMIN(ADMIN)=> select * from Test_Recovery;
NUM | DESCRIPTION
—–+————-
1 | ABC
2 | XYZ
(2 rows)

TESTDB.ADMIN(ADMIN)=> delete from Test_Recovery where NUM=1;
DELETE 1

TESTDB.ADMIN(ADMIN)=> select createxid, deletexid, rowid, * from Test_Recovery;
CREATEXID | DELETEXID | ROWID | NUM | DESCRIPTION
———–+———–+——————+—–+————-
91389590 | 0 | 1051578600800001 | 2 | XYZ
(1 row)

TESTDB.ADMIN(ADMIN)=> show show_deleted_records;
NOTICE: SHOW_DELETED_RECORDS is OFF
SHOW VARIABLE

TESTDB.ADMIN(ADMIN)=> set show_deleted_records=true;
SET VARIABLE

TESTDB.ADMIN(ADMIN)=> show show_deleted_records;
NOTICE: SHOW_DELETED_RECORDS is ON
SHOW VARIABLE

TESTDB.ADMIN(ADMIN)=> select createxid, deletexid, rowid, * from Test_Recovery;
CREATEXID | DELETEXID | ROWID | NUM | DESCRIPTION
———–+———–+——————+—–+————-
91389588 | 91389980 | 1051578600800000 | 1 | ABC
91389590 | 0 | 1051578600800001 | 2 | XYZ
(2 rows)

TESTDB.ADMIN(ADMIN)=> update Test_Recovery set DESCRIPTION=’AAA’ where NUM=1;
ERROR: Concurrent update or delete of same row [tbl 212201676 dsid 36 tx 0x57281f0 U prev 0x572801c]

Teradata – Database to database grants for views

SCENARIO:

We have a database DB1_t which have all the tables and a database DB2_v which have views pointing to database DB1_t.
Now if we have a new database DB3_v which will have views poniting to database DB2_v, do we need to provide database to database access to DB3_v on DB1_t with grant option?

TEST:

Create Database DB1_t From dbc As Perm=1e10, No Fallback, No Journal;
Create Database DB2_v From dbc As Perm=1e9, No Fallback, No Journal;
Create Database DB3_v From dbc As Perm=1e9, No Fallback, No Journal;

create table DB1_t.T1 (Counter number, Descr char(10));
insert into DB1_t.T1 values (1,’a’);
insert into DB1_t.T1 values (2,’b’);

Select * from DB1_t.T1;

REPLACE VIEW DB2_v.V1 AS LOCKING ROW FOR ACCESS SELECT * FROM DB1_t.T1;
grant select on DB1_t to DB2_v with grant option;
select * from DB2_v.V1;

REPLACE VIEW DB3_v.V1 AS LOCKING ROW FOR ACCESS SELECT * FROM DB2_v.v1;
grant select on DB2_v to DB3_v with grant option;
select * from DB3_v.V1;

REPLACE VIEW DB2_v.V1 AS LOCKING ROW FOR ACCESS SELECT * FROM DB1_t.T1;
select * from DB3_v.V1;

drop VIEW DB2_v.V1;
REPLACE VIEW DB2_v.V1 AS LOCKING ROW FOR ACCESS SELECT * FROM DB1_t.T1;
select * from DB3_v.V1;

RESULTS:

We only need to provide grant on DB2_v to DB3_v. No need to provide grant on DB1_t to DB3_v.