Jump to content
Lars Fosdal

Best site/source for SQL Server questions?

Recommended Posts

The joys of inherited problems.


I am looking for a pattern or advice on how to best allocate a set of records to a specific requestor when there are multiple parallel requests, and there are multiple parallel request viewers.

In practice: Allocate a set of deliveries to a user, when there can be multiple users requesting deliveries in parallel, and multiple users viewing the deliveries in parallel.

 

The current SQL can give deadlocks both on allocations and on viewing, so something is definitively not right, and I need to improve on it.

 

 

 

There is SO, where it all is an exercise in asking the question right...

Are there other good discussion sites?

Share this post


Link to post
2 minutes ago, Attila Kovacs said:

WITH (NOLOCK) ?

Well, I've added that where it seems safe for the viewers, but it is a short way between being a viewer and being a requestor, so for certain lookups, the data needs to be accurate.

The entire allocation part is a very heavy SQL statement with joins, functions, conditions and sorting - and the actual grabbing is totally void of transactions.

 

Share this post


Link to post
Posted (edited)

I see, I'd check then "Row Versioning". I never used it yet but I'll try it myself when I find some time.

Edited by Attila Kovacs
  • Thanks 1

Share this post


Link to post

Yes, I can see that from your link. It came as a surprice to me, as I am mostly used to work with IB and Firebird databases.

 

There is at NOLOCK isolationlevel, that will minimize risk of locking, but it will not be suitable, if there is a risk of data being changed during the execution of the query. Another thing to do is to make sure that all the fields in the where clause and the joins are indexed to avoid tablescans.

Share this post


Link to post
On 3/12/2019 at 12:44 PM, Hans J. Ellingsgaard said:

There is at NOLOCK isolationlevel, that will minimize risk of locking, but it will not be suitable, if there is a risk of data being changed during the execution of the query. Another thing to do is to make sure that all the fields in the where clause and the joins are indexed to avoid tablescans.

4

I usually limit NOLOCK to queries for
- data that is used as viewing info (i.e. not acted upon)
- data that are currently in the custody of the client (i.e. not likely to be changed by other processes)

Share this post


Link to post

some thoughts and questions to this

- the only requests like this one I know from ms sql "users"

- so some systems seem to be picky about row level locking others not

- the sql handling problem itself here is bound very close to the real world problem of demanding a physically limited thing

- row level locking in ms sql server under load could cause «lock escalation> aka a much more general locking like page locking

- maybe one has to think about some denormalisation, which of course have to be accompanied by accompanied by adequate constraints, keys, triggers, etc.

- handling the «grabbing» might be easier, quicker, more robust, if handled by a single, atomic Grab Stored Procedure (considering the denormalisation)»

 

- which version is in use?

- could you elaborate «..the actual grabbing is totally void of transactions..» please?

- or describe the workflow a little?

 

Share this post


Link to post
On 3/16/2019 at 2:40 PM, jobo said:

- handling the «grabbing» might be easier, quicker, more robust, if handled by a single, atomic Grab Stored Procedure (considering the denormalisation)»


That's why I'd like to find an SQL group to get some hints to best practices

 

- which version is in use?

2008r2, 2012, 2016
 

- could you elaborate «..the actual grabbing is totally void of transactions..» please?

Some of the current code does not do explicit transaction handling at all.
 

 

- or describe the workflow a little?


That would become very detailed, very quickly

 

2

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×