Sure I understand that. But in the context of the suggestion?
-
Join the MyBroadband community
South Africa’s biggest forum. Discuss, discover, and connect with thousands of members.
-
Question of the Day: What's the most interesting small town you've visited in South Africa?
SQL - Retrieving work
- Thread starter Marcell1992
- Start date
![[)roi(]](/forum/data/avatars/m/8/8711.jpg?1563828683){kind=avatar}
Contention...
Handling millions of events per hour is simple if there is no update contention, not so when there is.
Yes, but in the OPs case there is hardly that
The OP has described a contention scenario I.e. Where more than one data capture clerk tries to update the same record set; a rollback transaction won't resolve that.
This sounds exactly like that..
...the job of the data capture clerks I assume is to capture the metadata for the scanned PDF; most likely an update as opposed to an insert.
Exactly, but one also need to understand what in their workflow is serving up the same work to more than one data capture clerk. I can only assume; it's a case of poor contention design where a SQL query simply checks a flag; which under load can result in more than one clerk contending for the same update.
Locking can resolve that but so too queuing.
Last edited:
Kosmik
Honorary Master
yep, a basic LIFO stack would work, something to add to the stack on upload and a manager to pop items off the stack for assignment, manager being a single instance of code.
WHy LIFO in particular. OP didnt tell us whether there are priorities etc...
Kosmik
Honorary Master
Sorry meant fifo, having a off day today.
In my example the update is not important at all. It is the insert that prevents assigning a job multiple times
sounds like someone from your IT team solved the locking issues they experienced with "select * from xyz with(nolock)" to try and alleviate the performance issues they have. the issue with that, and transactions (hoping they have that in place) is that you get, what is called, a "dirty read", which means the same record can go out to multiple people as you've experienced.
Kosmik
Honorary Master
That thought also crossed my mind.
Kosmik
Honorary Master
Update and Insert should both create a lock on the row. Insert may be longer due to initial index/statistic creation.
Marcell1992
Well-Known Member
- Joined
- Aug 7, 2018
- Messages
- 289
- Reaction score
- 59
A lot of these posts go over my head, I'll just copy and paste it into an email for IT and hope for the best.
Quite embarrassingly it's been an issue for years, it just gets worse the more popular my company becomes. I've just started getting really fed up that IT is not making progress, so I thought I'd ask the good ol' MyBB.
We have on our busiest season around 11000-13000+ forms scanned per day, not so busiest is 8000 or so.
Quite embarrassingly it's been an issue for years, it just gets worse the more popular my company becomes. I've just started getting really fed up that IT is not making progress, so I thought I'd ask the good ol' MyBB.
We have on our busiest season around 11000-13000+ forms scanned per day, not so busiest is 8000 or so.
Sure, you avoid the final update conflict, but you don't avoid the contention; because the sanctity of the process you've defined hinges on exceptions / rollbacks.
Which is an anti-pattern, for example:
- Don't use exceptions for control flow
- POLA : Principle of least astonishment
Last edited:
As @WAslayer surmised I think your best way to cleanly resolve this would be rearchitect part of the workflow to use a proper message queuing solution, however as a quick and dirty you could consider @Spacerat proposal, which would also avoid two or more clerks interpreting the same scans more than once including avoiding conflictis during the capturing of the metadata.
Last edited:
Kosmik
Honorary Master
TBH, you are probably looking at a legacy issue in design. Also, depending on your IT team, they could be out of their depth ie: don;t expect techs or a network admin to do/troubleshoot a dba or developers job.
sounds like you might work for docIT or someone who might have implemented it 4 years ago
agree 100%, just thought it may be a quick fix retrofit / bandaid. But I think there is bigger architectural issues