1

Topic: ...

There is a table, the big file there boots. That is approximately 40 flows parallely do :
BEGIN TRAN
SELECT * FROM T WHERE FIELD =?
If there is a record
UPDATE T SET... WHERE FIELD =?
Otherwise
INSERT INTO T...
COMMIT
The part of records falls off with an error that that like deadlock detected victim...
I suspect there is it because of:
The flow 1 fulfilled  on record SHAREDLOCK, the second () fulfill  and put honey agarics SHAREDLOCK on the same record, anybody from them cannot fulfill UPDATE because of set SHAREDLOCK .
SELECT WITH (UPDLOCK) does not help.
How here to organize that that like  SELECT FOR UPDATE? What would put at once WRITELOCK at  till the end of transaction? Or can eat better variants?

2

Re: ...

no56892;
It is necessary  to catch the graph .
Before it it is possible to look at the actual execution plan of similar requests.
The big file it is necessary   in the staging-table and in any row-wise hogwash to be engaged on the staging-table.

3

Re: ...

In the field FIELD there is an index?
Load  a file line by line?

4

Re: ...

wrote:

the Big file it is necessary   in the staging-table and in any row-wise hogwash to be engaged on the staging-table.

Not a variant

wrote:

load  a file line by line?

Yes, there it is conditional in it there are elements which on one are read and scattered on 40 flows, whose task actually and to push in basis on one.

wrote:

in the field FIELD there is an index?

Did, did not help. Can not that type? Nonclustered, field VARCHAR

5

Re: ...

It is necessary to raise isolation level

6

Re: ...

In advance

7

Re: ...

Or new indexes to create.

8

Re: ...

What type there at FILEID? int?

9

Re: ...

Varchar (30). It is By practical consideration installed that select with (update) puts U lock on ROW, and also IU on PAGE. I so understood IU  and of it not to get rid?  all  like, yet  , the second transaction hangs on  waits that apprx. Truth tested on other  the server, adjustment Mb different at them, but it hardly...

10

Re: ...

1.  to consider  without its graph.
2.

no56892 wrote:

BEGIN TRAN
SELECT * FROM T WHERE FIELD =?
If there is a record
UPDATE T SET... WHERE FIELD =?
Otherwise
INSERT INTO T...
COMMIT

This beauty to replace with one MERGE. Or, at least, to remove SELECT.

11

Re: ...

no56892;

wrote:

yet

It not quickly. It is necessary to wait while it invent.

12

Re: ...

no56892 wrote:

40 flows parallely do :
SELECT * FROM T WHERE FIELD =?

on ...
- Scatter on flows the data with different FIELD that the probability of their intersection was minimized
- To do in three steps (if it is admissible from the point of view ):
1. To receive from a file unique FIELD
2. To read the table marking what FIELD is what is not present (to read from table PK if to eat and add in
Storages in structure  FIELD - PK)
3. To do update
4. To do
- The table to partition that the separate flow flew in the section (sections then to unite if it is necessary)
It so, generally and, a reality only after the graph

13

Re: ...

Varchar (30) class type;
You tried to do normalization?

14

Re: ...

There Chyo ?

15

Re: ...

no56892 wrote:

it is passed...
Not a variant

Well... Continue to toil with a hogwash... In forty flows.

no56892 wrote:

How here to organize that that like  SELECT FOR UPDATE? What would put at once WRITELOCK at  till the end of transaction? Or can eat better variants?

with (holdlock)
no56892 wrote:

Or can eat better variants?

As on you already reported, it is better - BULK INSERT. Though in forty flows.
And then... merge in one.

16

Re: ...

aleks222;
No, a key long

17

Re: ...

main. BMSG:
BMSGID NUMMERIC (18,0) NOT NULL
BMSG_NUMBER VARCHAR (30) NOT NULL
BMSG_PAYLOAD VARCHAR (255) NULL
NONCLUSTEREDINDEX (NON UNIQUE, NONCLUSTERD) ON BMSG) NUMBER

18

Re: ...

invm wrote:

This beauty to replace with one MERGE. Or, at least, to remove SELECT.

40 flows MERGE? OMG...
How about sectioning or even a partition of the initial table on some?

19

Re: ...

It is by practical consideration installed  :
Select for update with (updlock) where x=y and then update where x=y work as well as .  yet  transaction with select with updlock,  select with updlock hang pending, all super,  such situation creates insert as that indirectly, here to understand as... A variant without ,   an update if 0 rows ffected that insert if unique constraint that again update is not desirable,  here soa and all affairs,  produces indirect service, the over-expenditure is undesirable... I can not understand in any way as  creates  here as there is a variant if deadlock, once again to repeat, but these are all half measures what that

20

Re: ...

40 flows are more important than efficiency.
It is possible to disperse.

21

Re: ...

Above I spread  the graph, on it generally it is clear nothing? And what  the server is not able to work parallely with ten sessions over one table? , above all offered as already wrote in my opinion half measures though  +  even will be faster, but here there are some restrictions (historical) on a call of the method a cat all it does (reads and floods a file)

22

Re: ...

I give for speculations a case of the decision of the similar task.
The rabbit sent messages to the DB table through call  (inside 1 insert), the maximum speed made 60 pieces in a second. At a certain stage this speed appeared insufficient. A problem began to solve two methods. The first was reduced to multi-threaded record, gave speed nearby 150 in a second and has been accepted in all organization, except my allotment.
The second method developed by me, allowed SSIS to climb directly in the bus and to extort messages a pack, then to understand with them as a whole. Average rate of handling (taking into account pauses) reached to 1000 in a second and above.
The description is strong in general therefore I lower waiting, acknowledgement,  and other pleasures. Ghost effect - colleagues addressed with the complaint that nobody listens to queue. And, no, there is a listener... Oh, again is not present! Correctly, after obtaining of pack SSIS it was disconnected (though it was possible it and not to do). After realignment of Zabbiksa of the colleague ceased to be anxious.

23

Re: ...

no56892 wrote:

Above I spread  the graph, on it generally it is clear nothing?

at you it is some processes  each other on operations SELECT and UPDATE trying to superimpose U lock on an index key.
Type so:
process1 SELECT waits "And"
process2 UPDATE Would block waits "And"
process3 SELECT Would wait
process4 UPDATE Would wait blocked "And"

24

Re: ...

Show plans of your requests as at you the index is used.

25

Re: ...

And how can block  holding  on And?  and an update go on the same value, those if the update Would be fulfilled on that it already captured U at previous .  I will try to make