Debezium transaction metadata - no `END` event received with SQL Server

[Mark Nuttall-Smith]

I'm trying to get the [transaction metadata][1] working with debezium 1.4.1.Final using a SQL Server database.

It seems to be working to some extent - the `dbservername.transaction` topic has been created, and when I run a stored proc which contains a transaction, then the `"status":"BEGIN"` event is received, along with the CDC packets on the table topic.

However, no `"status":"END"` event is received... until I run the stored proc again. 

It may very well be that I'm not closing the transaction in the stored proc correctly (I'm not a MSSQL expert by any means)...

This is the structure I'm using:

```sql

CREATE PROCEDURE schema.myproc

AS

BEGIN

    BEGIN TRANSACTION

        ...

    COMMIT;

END

GO

```

Any ideas what I need to do to get the `END` event at the end of the proc? 

  [1]: https://debezium.io/documentation/reference/connectors/sqlserver.html#sqlserver-transaction-metadata

[jiri.p...@gmail.com]

Hi,

unfortunately SQL Server does not provide a reliable way how to detect an end of the transaction (last event). So we can emit END event only when we see an event from a new transaction.

J.

[Mark Nuttall-Smith]

Hi Jiri,

Oh, that's pretty frustrating. I'm working with a low throughput system, but one where it's important that the consumer can group events in a transaction together.

Do you know of any workarounds? Perhaps executing two transactions within a stored procedure?

Otherwise - can I suggest this should be in the documentation, it's really surprising behaviour.

Cheers,

Mark

[jiri.p...@gmail.com]

I undertsnad your pain. We tried to solve the issue https://issues.redhat.com/browse/DBZ-2084 but it seems we cannot guarantee that the transaction is delivered atomicly to CDC tables :-(

Generally I'd say you either send two transaction or you'd intorduce a heuristic like if END has not arrived til for example 1 minute then tx is ended. But that's something that is business specifica and is impossible to handle at Debezium level.

You are right with the documentation.

J.

[Gunnar Morling]

Would it help if the Debezium connector for SQL Server had the ability
to emit "heartbeat queries", as done by the one for Postgres (see
https://debezium.io/documentation/reference/connectors/postgresql#postgresql-property-heartbeat-action-query)?
There'd then be "dummy" transactions at a given interval, which would
provide you with the END event for your actual transactions.

--Gunnar

Am Do., 11. März 2021 um 10:18 Uhr schrieb jiri.p...@gmail.com
<jiri.p...@gmail.com>:

> --
> You received this message because you are subscribed to the Google Groups "debezium" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/4a7ec334-5a26-473d-bb9e-d133bbe3e900n%40googlegroups.com.

[jiri.p...@gmail.com]

It sounds like a good proposal

J.

[Mark Nuttall-Smith]

Thanks for the replies.

For us it's important that the transaction is closed quickly after the work is done. I've worked around the issue by adding a second transaction to the SP that updates the table

CREATE PROCEDURE nl.myproc
AS

   BEGIN

       -- real work with nl.mytable

   COMMIT

   BEGIN TRANSACTION
       INSERT INTO nl.mytable VALUES ('TX_END', ...)
       DELETE FROM nl.mytable WHERE mykey= 'TX_END';
   COMMIT

It's pretty ugly, but this transaction boundary is enough to know that the real work is complete, and arrives immediately after the work is done. 

With the heartbeat idea - how frequently would such a heartbeat be configured?

Thx, Mark

[Gunnar Morling]

I've captured the heartbeat approach in https://issues.redhat.com/browse/DBZ-3261. Contributions very welcome.

> With the heartbeat idea - how frequently would such a heartbeat be configured?

That'd be up to you really. The Postgres connector has an option "heartbeat.interval.ms" for controlling this.

--Gunnar