Netezza does not update records in place, it marks records with a delete flag. In fact, each record contains two slots, one for create Xid another for delete xid. Delete xid allows us to mark a record with a current transaction for deletion, up to 31 transactions are allowed in Netezza for all tables. As noted earlier, only one update at a time allowed on the same table though. Here update refers to transactions that are not committed yet. Coming back to delete xid, this is how Netezza maintains transaction rollback and recovery. Once a record is modified, its delete xid is given transaction id; this is changed from the previous value of 0, all records when loaded will contain 0 for delete xid. Note that FPGA uses its intelligence to scan data before delivering them to host or applications.
Sample data:
[ROW id][Create xid][Delete xid]
[R1][T1][0] // First time a record is loaded, record R1
// After some time, updating the same record
[R1][T1][T33] // Record R1 is updated; note T33
[R33][T33][0] // New update record R33; similar to a new record this has zero for Delete Xid.
If the record is deleted, simply deletion xid will contain that transaction id.
Based on the above, how do you know a record is the latest. It has zero in delete xid flag.
Extending the same logic, how do we know a record is deleted. It has a non-zero value in the delete xid flag.
How do you roll back to the transaction? Follow similar to the above listing, we can roll back a transaction of our interest.
Note that the transaction id is located in the create xid flag and that is our point of interest in this case. From what I know, row id and create id is never modified by Netezza.
