SQL Server: deadlock victim. Rerun the transaction -

i'm aware error has been covered, it's more advice relating wider picture i'm after really.

i have account restricted select on handful of tables in database owned third party application. idea being can safely query odd thing here , there without having request third party make changes (they're not responsive).

this has (ostensibly) been working fine until earlier today when noticed exception in logs (see post title).

given query was:

select      datediff(ss, '01-01-1970 00:00:00', (select max(createddate)                                           visits                                           custid = 31)) 

and dbcc useroptions tells me isolation level read committed, why should above conflict anything? can understand getting outdated value, why conflict on committed-only read?

anyway, what's worrying me more might happening on other end. if third party app experiencing similar errors , nobody has noticed (they wouldn't in hurry)?

can put mind @ rest other end (again, i'm doing selects), , possibly advise on best practice in sort of scenario? sql concurrency isn't i've had worry in past.

thanks

this sort of deadlock has access path value in table , indexes affected. query above contains aggregate (max) @ minimum (no pun intended) have go find max value among rows requested customer. there may index supports that. during search, though, if brief, data needs locked against updates. @ same time update query may come along accessing data using different sequence of indexes, , needs exclusive lock allow updates. yes, read committed isolation , right update , select can deadlock.

the best long term resolution rcsi (read committed snapshot isolation) allows reads use "old" versions of rows instead of live/hot value being modified in table. if have third party app, though, vendor have test , support setting before can turn on. don't turn on. it's great feature needs testing specific apps. said, changes typically required minimal or none, exceptions.

failing that, careful study of deadlock graph real event @ server can yield way change indexes, possibly, prevent deadlock.

lastly, can read uncommitted data instead, consequence may not 100% accurate time.