When I have the SQL Server Management Studio open and running a select query and I am running the OS Platform in debug mode, I noticed that the query would hang while any OS Action that operates on the same table being queried is executing.  This is not just for the query in the Action itself, but for the duration of the entire Action.  As soon as the Action completes (exit on End), the SQL SMS query resumes executing.  This is being run on our development platform.  Haven't tried it on our production platform yet.

Question:  How would SQL SMS know that an OS Action is executing a query against a particular table?  Is there some form of 'locking' going on here?  Does anyone have any experience with this?  Could this be a performance issue.  If SQL SMS goes into in a 'wait' mode while an Action is executing, can this also happen in the OS Platform itself?  Or is the OS Platform doing some kind  screen or action level locking only when running in debug mode?

Bob -

Yes, it's locking. It's expected behavior. If you look at the underlying code in SQL Server Profiler, it is not passing the "No Lock" hint, which means that for the duration of that query, the rows are locked. Yes, this isn't good for performance, but it is critical for data integrity.

That said, I would LOVE to see a check box/property on a query for "Use No Lock", for those who understand the consequences of it.

J.Ja

Thanks Justin. 

I would really like to find out more about how the OS Platform is handling locking.  As I said above, the locking seems to be for the duration of the Action (at least in debug mode); almost as if the Action is wrapped in a transaction that locks the tables until the Action ends.  If this is the normal behavior of the Platform, I can see a lot of instances where performance could be very significantly degraded.  I am particularly interested in this as a performance issue because the OutSystems app we are running is experiencing very slow performance.

When our app initially opens, it populates the user's screen using some fairly heavy Advanced queries, each of which executes several sub-queries.  These are 'select' queries, but there are also other queries doing updates to track user activitiy.  My concern is that if the Preparation Action is locked by a user during its execution, then other users must wait for that action to complete.  If this is how the app is behaving, then I have an explanation for the poor responsiveness we are experiencing.

I would appreciate anyone's thoughts or experience with OS Platform locking and performance,

I have another question about this subject.  If the Platform is locking table rows, then what is the purpose of the GetEntityForUpdate actions which I thought was specifically to instruct the Platform to lock a record before updating?  

Bob -

1. I *suspect* that the locking is indeed for the duration of the Action, though that said, sub-Actions can certainly do stuff just fine. I think some experimentation or a ticket with support will give you the exact answers you need.

2. GetEntityForUpdate is indeed different from GetEnity (first locks, second does not). HOWEVER... I have a strong suspicion that a simple query does some sort of locking at some point in the process, based on behavior that I have seen recently.

3. Your Advanced Queries should allow you to pass NOLOCK, so if this is a big concern, I'd suggest that route... though honestly, I discourage Advanced Queries as much as possible, they make maintenance, changes, refactoring, etc. MUCH harder than they are with simple queries.

J.Ja

Thanks again Justin,

From your info I am getting a much better understanding of what's going on in our app.  And I agree 100% about the use of Advanced Queries.  Our next task is to refactor the code to replace them with simple queries.