0

I have a SSIS job that copies data from one table to another. The tables are identical in setup. The process truncates the destination table then uses a OLE DB Source Editor tool to copy the full table from Source to Destination. My question is: Does the source table get locked during this process?

Thanks Rob

edit- I have had very little responses for my question which leads me to think it is not clear. Please see below a screen shot of my setup: enter image description here

When the OLE DB Source Editor task is run will the nominated table be locked during the process? Surely someone can give me a straight forward answer please.

RK43
  • 155
  • 2
  • 9
  • Yes but there are many kinds of locks. For example there would be a schema lock (can't change table columns but can read and write data). There'll be an exclusive table lock if you add `WITH (TABLOCKX)`. There might be page or row locks. – Nick.Mc Nov 19 '19 at 12:31
  • Thanks Nick. There is no SQL (so no chance to put WITH (TABLOCKX) command. The data access is 'Table or view' then the table is specified. In this case is the table locked? – RK43 Nov 19 '19 at 12:46
  • I already gave a straightforward answer: Yes there are locks. But locks are not straightforward. https://stackoverflow.com/questions/12608780/understanding-sql-server-locks-on-select-queries. By default the locks that are used are "compatible" with other selects and inserts, meaning that while this is running, a different session can select, insert and update the table. What problem are you trying to solve? what prompted this question? – Nick.Mc Nov 20 '19 at 01:02
  • 1
    By locked, maybe you mean "exclusively locked" (so that you are unable to modify data) and the answer is: never in my experience. It might be that the SQL server lock manager escalates to table lock. Locks change over time depending on the database config and and the activity occurring at the time. Maybe if your database was at isolation level _serializable_ and you explicitly enabled transactions in your data flow it might be locked for reading and writing. I only mention these caveats because there is always an edge case to be considered. – Nick.Mc Nov 20 '19 at 01:13
  • If you want to see locks, I suggest you install `sp_WhoIsActive` and observe. You'll see that you get all kinds of different locks over time and they generally do not cause issues for other operations. Here's a good article on different types of locks: https://aboutsqlserver.com/2011/04/14/locking-in-microsoft-sql-server-part-1-lock-types/. From the article: _SQL Server has more than 20 different lock types... Shared locks (S). Those locks acquired by readers during read operations such as SELECT_ – Nick.Mc Nov 20 '19 at 01:17

1 Answers1

0

As confirmed in the comments locks are complicated, more complicated than I realised. My concern was that running the SSIS package would lock tables that stopped other systems making modifications. I now understand that this will not be the case. I believe that it will do a dirty-read meaning that the table can be modified while I extract the data. So I am happy with that. Cheers to Nick.McDermaid for his time.

RK43
  • 155
  • 2
  • 9