Luke101 Luke101 - 4 months ago 35
C# Question

Prevent database locks when uploading a file in a transaction

I have a feature where a user can view all their uploaded files. To accomplish this I am inserting a record in the database then uploading a file to AWS S3. I am using the id from the record for the file name. For example, if the id is 53 then the file name is

53.pdf
. Both inserting the record and the file upload are in a TransactionScope. I am worried the database will be locked during this long operation. Is there a way to prevent the database from being locked?

Answer

I am worried the database will be locked during this long operation. Is there a way to prevent the database from being locked?

You need not worry about database being blocked,the only transactions that will need an exclusive locks on database are Alter database statements,these require exclusive locks and can potentially block whole database.

You need not worry about object level transactions blocking database

below is sample locks screenshot, taken when i ran two inserts statements in parallel..As you can see for Insert ,SQLServer is acquiring(IX lock ) on object which states "i am going to lock something exclusively on layer below table..."

So in this case ,if you are going to run another transaction which needs to select id from same table and insert it to same table,select is going to be blocked(on Normal SQLServer running Default Isolation level)..But not Insert...

enter image description here

But SQLAzure, by default runs on RCSI which in simple terms means `Reading sessions won't be blocked' ..

I am not sure ,if this is the case with SQLServer in AWS as well??..You can check this by running below query (Check the isolation level desc column )

select * from sys.databases