![]() ![]() If I disable the hop from 2 to 3, the job runs fine, and it takes just a couple of minutes. If I kill either session, the transformation fails, with no records written the sprocs in steps #3 or #4 sometimes run, but with no new data on which to operate. Let's imagine: 1 - Transaction A locks row1 2 - Transaction B locks row2 3 - Transaction A tries to lock row1, and, because of the previous lock, SQL server waits 4 - Transaction B. The TRUNCATE never completes (though Postgres does not report it as blocked), so no records get written and step #3 cannot run. Deadlocks occurs because, two concurrent transactions may overlap e lock different resources, both required by the other transaction to finish. It appears to be deadlocking: checking for blocked sessions on the target database (PostgreSQL), I see two sessions, one for the stored procedure in step #3 (which shouldn't have started yet), which is blocked by a session which is trying to truncate the table in step #2. The GUI itself does not hang, in fact the Stop button disables after a moment, but the transformation does not proceed. You can easily see the SQL deadlock victim (shown in red), as well as lock types, all in the order they were applied. The host, application, and resource names are all labeled in the graph. It consistently hangs (tested several times, waiting up to 1.6 hours) that is, Pentaho reports starting the job in the Logging window, then nothing more. With SQL Sentry, there is no need to translate SPIDs or reference IDs. These have hops between them: 1 to 2 to 3 to 4. Run a stored procedure against the table modified in #3.Run a stored procedure against the table populated in #2.Write to a staging table, with truncation (import table).Read a pipe-delimited text (a text file input step).Adding pages concurrently can result in deadlocks. A deadlock problem occurs when two (or more than two) operations already want to access resources locked by the other one.If you are using MS SQL Server, check if you are affected by this known deadlock problem.Disable any Remote API scripts or disable the Remote API from Administration > General Information. A script or Remote API process is hammering the server.Check that page for diagnosis and resolutions. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |