If SQL Server Management Studio is not installed, you can test the connection using Telnet: If SQL Server Management Studio is installed on the EMS Sever, use it to test the connection to your SQL Server. Troubleshoot W900B or W906B System Connection Errors as follows:ġ. Even if it is more expensive.The existing Microsoft SQL Server must be a version from 2012 onwards.
#Ems sql manager locking tables update
For this use case with Table_Comparison there is no problem, we always compare first and then update the target row, one row after the other.Īctually, with SQL Server 2008 I had to do quite some tweaking to get a hanging or deadlock error at all, nevertheless I prefer the Oracle way. So go to the Data Services bin folder of the jobserver, open the file DSConfig.txt and in the AL_Engine section add the line SQLServerReadUncommitted=1. We can ask SQL Server to perform the select without applying a read lock. Turn on dirty reads: If you are absolutely sure you read the data first and then you update it, we do not care what data we read.Run Table_Comparison in cached mode: Now the reading happens first, before the table loader start even with the writing.SQL Server 2008 is getting better in that regards as well and it even supports the same mode as Oracle, the mode is called READ_COMMITTED_SNAPSHOT. With Oracle this cannot happen as there a select does not require a lock. With sorted-input mode the tool did hang after processing 1000 rows. In my environment and with my data, when Table_Comparison was issued in row-by-row mode I got a deadlock error, so the database did get the select and updates in an order that qualified for a deadlock. The database has no way to figure out that these two sessions depend on each other from within the ETL flow in most cases. But if there Loader cannot commit because it is waiting for more rows to come - which don't - it appears as if Data Services is hanging. As soon as the loader committed the data, the reader can continue. It is not a classic deadlock situation because as seen from the database, the one wants to read data but can't because the array is written at the moment, so the reader has to wait. If both touch by coincidence the same rows at the same time you get a lock collision. The target table will update rows and the Table_Comparison Transform reading them. I have created a simple dataflow with Table_Comparison (row-by-row) and a Target table. The problem with SQL Server is that it requires locks even for reading and that it sometimes switches to page or even table level locking for large amounts of insert/update/deletes. And the solution is simple, using the include-in-transaction flag or merge the data so that there is just one loader. On the first row the second session will already get the wait signal, wait until the other session committed its data and then continue.Īs you see, there are a lot of conditions that need to happen for a classic deadlock. Note: If they are processed in the same order, above deadlock situation will never happen. So if the same target table is used in parallel and the same rows are updated/deleted at the same time, a deadlock can happen if by coincidence the current array of both sessions include at least two rows with the same key and they are processed in reverse order. Such a situation can happen with any database as usually Data Services creates one session per table loader and the tool uses array processing options of the databases. waits for Session1 to commit the data which will never happen as Session1 is waiting on me.