Optionally guarantee uniqueness of crdb_internal_mvcc_timestamps

[dain]

Is your feature request related to a problem? Please describe.
crdb_internal_mvcc_timestamps can be used for building audit history and change data capture systems. Unfortunately crdb_internal_mvcc_timestamps is only unique for "causally dependent transactions” so such a system can not determine if two changes are the same or different transactions.

Describe the solution you'd like
Have a way to guarantee that causally dependent transactions is unique.

Describe alternatives you've considered
There could be another system column to establish uniqueness of the transaction commit. An ordering could still be established with crdb_internal_mvcc_timestamps and the new system column could be used to disambiguate transaction collisions.

Jira issue: CRDB-6210

Epic CRDB-60946

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

@dain Thanks for this request. This isn't my main area of work, but I think these timestamps originate at a pretty low-level, so it may be a big project to implement. We'll keep this request in mind though.

[mwang1026]

How often have you seen transaction timestamps be the same across two transactions? More curious than anything, understanding that for certain use cases even the hypothetical that they could collide isn't useful.

I'm also assuming you mean transactions at the SQL layer rather than the KV layer. We track the notion of a transaction ID at the KV layer but with parallel commits and whatnot, that doesn't tie into the SQL transaction ID.

How familiar are you with our https://www.cockroachlabs.com/docs/v21.1/sql-audit-logging.html capability? How is it the same/different than what you''re looking for?

I'm probing on the problem / use case here because the MVCC timestamp is an internal construct to allow us to manage transactional isolation. If we were to now tag all data with one global SQL transaction ID that could add quite a bit of overhead and negative impact to performance, so want to understand the need here to explore other solutions to that need/use case.

[dain]

The point of this request is to have guarantee uniqueness. Guaranteed uniqueness enables trivial implementation of a bunch of application features. Off the top of my head (not too much deep thought here), you can quickly determine which rows in the current database were committed together. If you commit a simple "bob is running this transaction" row to a table, you have built in auditing for current rows. Throw in CDC and you have a really powerful record keeping system.

The SQL audit log system you mentioned above looks very nice, but it appears to capture SQL statements. A system like this would allow you to track current DB rows to transactions... so one is "commands" and the other is more "results".

As for use case, I was looking at this for an auditing system as I described above and started a discussion on slack. After a quick discussion it was clear that the current system is close but not perfect. I would guess you could get to perfect by combining the current ID with a unique machine ID for the transaction coordinator and a unique counter on that machine. I expect there are some difficulties with machine crashes and clock synchronization, and am guessing you might have to delay during startup for a full global clock sync timeout (IIRC 4.5s). Anyway, this is mostly an idea and the questions "is it posuble?" and "how difficult".

[mwang1026]

Quick response! Thanks for that.

I think the answer is it's possible, but I think to your point what's unclear is "how difficult" and "with what tradeoffs". Without having dived (diven? doven?) deep into the solution space I don't know the answer off the top of my head.

But it's helpful to know that guaranteed, globally unique tracking of transaction ID wrote to/ performed operation on [rows] at timestamp by user1 is what you're looking for here.

Do you have any strong feelings on the format / how to access the audit log? You mentioned CDC but not sure if that's a preference or a nice-to-have (vs. say writing to log file on disk or to some network log storage endpoint)? Is there a specific DB that does the whole experience well that we should look to for inspiration? Kudos for a distributed DB

[dain]

Do you have any strong feelings on the format / how to access the audit log?

My assumption is there would be a unique ID stored with the row. If you have that, then you can simply add a table and write a row for each transaction to annotate a transaction with any data your want. For example, I could use such a system to annotate transactions with trace token from the application. So, I'm really not asking for a specific implementation, but a tool that can be used for building a system like that.

As for CDC, my expectation is the unique id would be included in CDC records, so this uniqueness could be utilized there.