Forum Discussion
Resolver IFabric Warehouse blocking caused by a sleeping Spark session with an open transaction
Hi everyone,
I've been running a Fabric notebook that writes data to a Warehouse table for about 1.5 years without any issues.
The notebook uses this code:
sanitized_df_df_clean.write.mode("overwrite").synapsesql(target_tbl)
Recently, I had a problem where several Power BI refresh queries were blocked.
After checking the Warehouse sessions, I found that session 52 was sleeping but still had an open transaction (open_transaction_count = 1).
Looking into queryinsights.exec_requests_history, I noticed that the Spark connector created two sessions.
Session 52 executed SET IMPLICIT_TRANSACTIONS ON and some metadata queries (SELECT TOP(1) and sp_describe_first_result_set), but apparently never committed or rolled back.
Session 55 executed TRUNCATE TABLE, loaded the data using COPY INTO, and successfully committed the transaction.
Both sessions belonged to the same Spark application.
I ran KILL 52, and the blocking disappeared.
The strange part is that I reran the same pipeline afterward, without changing anything, and everything worked normally. No blocking and no open transactions.
Has anyone experienced something similar with the Fabric Spark-to-Warehouse connector?
Why would a session remain sleeping with an open transaction after the notebook finishes? Could this be related to a recent Fabric update? Is there a way to prevent this from happening again without changing the entire notebook or pipeline?
Since this has been working fine for 1.5 years, I'd really like to understand what might have changed.
Thanks in advance for any suggestions!
Hi BIstvan
The two SQL sessions have independent transactions, even though they belong to the same Spark application. Session 55 committing the TRUNCATE/COPY INTO operation would not commit anything on session 52.A sleeping session is idle, not disconnected. With SET IMPLICIT_TRANSACTIONS ON, a SELECT that accesses a table can start a transaction that requires an explicit commit or rollback. Consequently, a connection used for metadata/read operations can remain idle with an open transaction if its transaction cleanup does not complete.
This behaviour is documented in SET IMPLICIT_TRANSACTIONS.Your evidence makes connector-side connection/transaction cleanup a reasonable suspect, but it does not establish a specific connector bug or Fabric update. A successful unchanged rerun is compatible with an intermittent problem; it does not explain the earlier failure. Because the connector is bundled with the Fabric runtime, unchanged notebook code does not necessarily mean an unchanged execution environment.
One important qualification: an open read transaction alone does not explain the exact blocking chain. Fabric Warehouse uses snapshot isolation, while schema locks can still cause blocking. Capturing the held/requested locks and any intermediate blockers is necessary before attributing the Power BI blocking directly to the metadata queries. See Fabric Warehouse blocking guidance.
I would keep the existing notebook logic and take these targeted steps:
- Capture evidence before terminating a recurrence. Collect sys.dm_exec_sessions, blocked requests from sys.dm_exec_requests, granted/waiting locks from sys.dm_tran_locks, and session-to-transaction mappings from sys.dm_tran_session_transactions. Inspect sleeping sessions directly, as they may have no corresponding active-request row. Preserve the relevant Query Insights history and UTC timestamps. The transaction-mapping DMV requires the Contributor workspace role or higher; involve a workspace Admin to capture other users’ sessions and requests.
- Compare the affected and successful runs. Record the Fabric runtime/environment, Spark application ID, pipeline run ID, session-reuse settings, and driver logs, particularly any timeout, cancellation, or retry around the connector call.
- Monitor and mitigate selectively. Alert on persistent open transactions that are causing blocking. A workspace Admin can terminate a confirmed abandoned blocker, but KILL rolls back that session’s uncommitted work. Do not automatically kill every sleeping session or hard-code session 52.
- Open a Fabric support case with that evidence. Microsoft can investigate the affected run using backend telemetry and deployment information to determine whether this was a cleanup defect or service regression.
There is no documented transaction-cleanup option in the current synapsesql connector guidance that I can recommend as a guaranteed prevention. Adding COMMIT, ROLLBACK, or SET IMPLICIT_TRANSACTIONS OFF through a separate SQL connection would not affect the connector-owned session.
Similarly, automatic session stopping documented for pipeline execution should not be assumed to guarantee cleanup of every connector-created SQL session. I would first confirm the session lifecycle with Microsoft Support rather than introduce an unverified session-management workaround.
Regards,
Srikanth Cheri
Microsoft Fabric Community Support
3 Replies
BIstvanv-csrikanth benibix Thank you for your support on this. I have added the notebookutils.session.stop(). Everything went back to normal. No lingering sessions remained open.
- v-csrikanth
Community Support
Hi BIstvan
The two SQL sessions have independent transactions, even though they belong to the same Spark application. Session 55 committing the TRUNCATE/COPY INTO operation would not commit anything on session 52.A sleeping session is idle, not disconnected. With SET IMPLICIT_TRANSACTIONS ON, a SELECT that accesses a table can start a transaction that requires an explicit commit or rollback. Consequently, a connection used for metadata/read operations can remain idle with an open transaction if its transaction cleanup does not complete.
This behaviour is documented in SET IMPLICIT_TRANSACTIONS.Your evidence makes connector-side connection/transaction cleanup a reasonable suspect, but it does not establish a specific connector bug or Fabric update. A successful unchanged rerun is compatible with an intermittent problem; it does not explain the earlier failure. Because the connector is bundled with the Fabric runtime, unchanged notebook code does not necessarily mean an unchanged execution environment.
One important qualification: an open read transaction alone does not explain the exact blocking chain. Fabric Warehouse uses snapshot isolation, while schema locks can still cause blocking. Capturing the held/requested locks and any intermediate blockers is necessary before attributing the Power BI blocking directly to the metadata queries. See Fabric Warehouse blocking guidance.
I would keep the existing notebook logic and take these targeted steps:
- Capture evidence before terminating a recurrence. Collect sys.dm_exec_sessions, blocked requests from sys.dm_exec_requests, granted/waiting locks from sys.dm_tran_locks, and session-to-transaction mappings from sys.dm_tran_session_transactions. Inspect sleeping sessions directly, as they may have no corresponding active-request row. Preserve the relevant Query Insights history and UTC timestamps. The transaction-mapping DMV requires the Contributor workspace role or higher; involve a workspace Admin to capture other users’ sessions and requests.
- Compare the affected and successful runs. Record the Fabric runtime/environment, Spark application ID, pipeline run ID, session-reuse settings, and driver logs, particularly any timeout, cancellation, or retry around the connector call.
- Monitor and mitigate selectively. Alert on persistent open transactions that are causing blocking. A workspace Admin can terminate a confirmed abandoned blocker, but KILL rolls back that session’s uncommitted work. Do not automatically kill every sleeping session or hard-code session 52.
- Open a Fabric support case with that evidence. Microsoft can investigate the affected run using backend telemetry and deployment information to determine whether this was a cleanup defect or service regression.
There is no documented transaction-cleanup option in the current synapsesql connector guidance that I can recommend as a guaranteed prevention. Adding COMMIT, ROLLBACK, or SET IMPLICIT_TRANSACTIONS OFF through a separate SQL connection would not affect the connector-owned session.
Similarly, automatic session stopping documented for pipeline execution should not be assumed to guarantee cleanup of every connector-created SQL session. I would first confirm the session lifecycle with Microsoft Support rather than introduce an unverified session-management workaround.
Regards,
Srikanth Cheri
Microsoft Fabric Community Support - benibix
Hi BIstvan,
What you found explains the blocking. With SET IMPLICIT_TRANSACTIONS ON, the first statement on that session (your SELECT TOP(1) and sp_describe_first_result_set) silently opens a transaction, and it stays open until something sends COMMIT or ROLLBACK or the connection closes. Session 52 never got either, so it kept its locks while sleeping. It's not caused by your code. The connection simply outlived the write, which can happen when the Spark application (or a shared high-concurrency session) stays alive after the notebook cell finishes, or when a run is interrupted between the connector's metadata step and its cleanup. That would also explain why the rerun was fine.
Two things that help without rewriting the pipeline:
1. End the Spark session at the end of the notebook. Put notebookutils.session.stop() as the last cell. When the Spark application ends, its JDBC connections close, and any transaction they still hold is rolled back.
2. A safety net after the write. Add a Script activity after the notebook (or a small scheduled check) that looks for idle sessions still holding a transaction:
SELECT session_id, status, open_transaction_count, program_name, login_name, last_request_end_time FROM sys.dm_exec_sessions WHERE open_transaction_count > 0 AND status = 'sleeping' AND last_request_end_time < DATEADD(minute, -15, GETDATE());
Kill those (as you did with KILL 52), or start by just alerting on them. Filter on program_name or login_name so you only touch your pipeline's own sessions.
Since it ran unchanged for 1.5 years, I'd also open a support ticket with the two session IDs and their queryinsights.exec_requests_history rows. A metadata session that never closes its implicit transaction looks like connector behaviour that Microsoft should see.
