I'm not quite following this. Your post uses a mix of terminology that isn't consistent. We need to try to reword it to more clearly articulate the issue.
I would like to ask if there is a way that upon creating a table or saving a record...
The "or" suggests you think these situations are synonymous or similar. Creating a table and "saving" (i.e. creating or updating) a record are completely different operations. Creating a table is a schema addition, done online or offline, but it doesn't touch application data.
...the record cannot be undone
"Undo" is not an operation on a
record. It is one of two outcomes for an active
transaction. The other possible outcome is a
commit. A user makes logical changes to a database within a transaction. Changes may include record operations (create, update, or delete) or index operations (create or delete). When the transaction commits, all changes within it are made permanent, or
durable. Any subsequent changes to the records or index keys will happen in later transactions (which may also commit or undo). When a transaction is undone, each of its changes is reversed by the server, by applying additional changes that have the opposite effects. The opposite operation of a record create is a record delete; the opposite of an index key delete is an index key create; etc.
For example, let's say a user wants to create a record in a table T1 and that table has two indexes, I1 and I2. As their client session executes the application code that makes their changes, the database tracks their changes in a new transaction and writes information about these changes in the before-image area; these are called
before-image notes. In this example, the following BI notes would be written, in order:
- transaction begin for transaction ID 13
- record create in table T1
- index key create in index T1.I1
- index key create in index T1.I2
If the user committed their transaction, one last BI note would be written:
- transaction end for transaction ID 13.
At that point, the user's changes would be permanent and would be visible to other users.
If instead the user rolled back their transaction (
undo) then more BI notes would be written:
- index key delete in index T1.I2
- index key delete in index T1.I1
- record delete in table T1
- transaction end for transaction ID 13
I encountered an instance that when the record is already saved but it get undone (undo), the saved record is gone
It is possible for a record to exist at one moment and then to not exist a moment later; it could be deleted. The time line could look like this:
time 0: the record does not yet exist
time 1: the record is created by user A in transaction X
time 2: the record is read by user B, who asserts its existence
time 3: the record is read and then deleted by user C in transaction Y
time 4: user B attempts to read the record and asserts that it no longer exists
As you can see, that sequence of events involved two committed transactions but no undos.
It is also possible to have a situation called a
dirty read, where a user sees uncommitted changes. Normally other users cannot see the changes being made by a user in a transaction while the transaction is active; we say that their changes are
isolated. But if another user queries the record being changed with a no-lock read, there is a chance they will see inconsistent or phantom data. Here is an example involving a dirty read and transaction undo:
- user A begins a transaction and creates a record, but does not yet commit
- user B queries that record (no-lock read) and sees it, thinking it exists (i.e. that it has been permanently created, or "saved")
- user A's transaction is undone, deleting the record
- user B attempts unsuccessfully to query the same record and in confused to find the record "gone"; in fact, logically it never existed
I should note that in my experience such real-world issues with dirty reads are rare.
You are claiming that the disappearance of the record is do to an "undo". How do you know that? How do you know it wasn't just deleted?