Blocking in a VL Table

Question

Blocking in a VL Table

VastSQL

SSC-Dedicated

Points: 36097
More actions
September 6, 2018 at 12:52 am

#415551

Hi Experts,
I have a table with more than 4 million records , I am updating values older than 2018 ,same time I am selecting values after 2018 . But the select is getting blocked by update how?
Update is on values older than 2018 and select is values after 2018.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

yes this is possible, the update could block any other query on that table, it would all depend on where the data is held and what lock your update has on said table.

if you ware wanting to query unrelated data, dear i say it :Whistling::hehe: you could use the nolock hint t look at unrelated data, there i have said the dreaded word :blink:

***The first step is always the hardest *******

ScottPletcher SSC Guru Points: 101097 More actions · Answer 2

How is the table clustered? If you query the table by date, cluster it by date. Then you won't have any issues reading historical data, and you could safely use NOLOCK if you wanted to, although it likely wouldn't be needed then.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 3

Thanks All.

How can I update a very huge table which will put exclusive lock not on all pages but on selected pages or extend. Is it possible to put exclusive lock on pages while progressing on update.

Knut Boehnert SSCrazy Points: 2951 More actions · Answer 4

Update in batches.

Approach it with a WHILE loop that checks for the same condition your UPDATE statement uses.
If found then do an UPDATE TOP(@n).
Depending on @n the update only uses a page/row lock.

One other difficulty one can find with such updates is log file running out of space.
This is simply SQL Server holding all transactions to commit in one big VLF (Virtual Log File) which just expands till it fills disk / maximum allowed size.
Running an UPDATE normally submits an implicit COMMIT TRANSACTION at its end.

Steve Jones - SSC Editor SSC Guru Points: 736193 More actions · Answer 5

You can read about log escalation, but if you are updating lots of rows, SQL might decide it's quicker to get an exclusive table lock rather than thousands of individual pages.

As mentioned, updating sets of records should help here.