There is a table, the big file there boots. That is approximately 40 flows parallely do :
SELECT * FROM T WHERE FIELD =?
If there is a record
UPDATE T SET... WHERE FIELD =?
INSERT INTO T...
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?