Database Locks

[Razorbx13]

Hi all, I have seen other posts here but am having an issue that does not appear to be explained in these posts.  So when I import new data is a lock occurring on the source database by PowerBI?  I am wondering because when I am importing records from a timekeeping system I will be reading records at a fast clip, but when someone goes and enters time my import slows to a halt, shows as suspended and the table gets locked.  Then after a few or several minutes, it starts flying again.  I will go from loading 15K records a second to 5 records a second.  We have a call into the software vendor as well, but I do not know why this would be occurring.  Any assistance would be helpful.  TIA.

[v-luwang-msft · Accepted solution]

Hi @Razorbx13 ,

I test it ,when I import data.

And I use the following to query if the table is locking in the sqlserver.

select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks where resource_type='OBJECT'

Retrun me blank.

So in my opinion, when exporting, there is no lock, you can use the above query statement to query the lock process when you import slowly.Generally, locking a table only occurs when there are additions, deletions, and changes to the table.

View locked tables：

select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks where resource_type='OBJECT'

spid   lock  Process
tableName   Locked Table Name

Unlocked:

declare @spid  int 
Set @spid  = 57  --Lock table process
declare @sql varchar(1000)
set @sql='kill '+cast(@spid  as varchar)
exec(@sql)

Does Select query Lock the table

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[lbendlin]

That is indeed a question for the software vendor.  I think the lock is generated during the write operation (as it should be, frankly)  and Power BI is the victim here, not the culprit.