Platform
Learn
Community
Support
Partner Center
Sign up
or
Log in
logout
Account Settings
Profile
messages
Messages
logout
Logout
Platform
Home
Downloads
IPP
Licensing
Project Sizing
Learn
Training
Documentation
Evaluation Guide
What's New
Community
Home
Forums
Forge
Ideas
Members
Support
Partner Guide
Resources Library
Opportunities
Account Management
Sign up
or
Log in
Home
Forums
Forge
Ideas
Members
Dear ,
What would improve your OutSystems Community experience? Let us know by taking this 2-minute survey.
Pick up the survey
Community
›
Forums
›
Technology & Integration
SQL Server Management Studio (SQL SMS) Lock Out
Community
›
Forums
›
Technology & Integration
SQL Server Management Studio (SQL SMS) Lock Out
New Post
New Post
Bob
Posted on 2014-02-25
Bob
Rank: #3540
Posted on 2014-02-25
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?
Justin James
mvp_badge
mvp_label
Posted on 2014-02-25
Justin James
mvp_badge
mvp_label
Rank: #4
Posted on 2014-02-25
Solution
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
Solution
Bob
Posted on 2014-02-25
Bob
Rank: #3540
Posted on 2014-02-25
Solution
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,
Solution
Bob
Posted on 2014-02-25
Bob
Rank: #3540
Posted on 2014-02-25
Solution
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?
Solution
Justin James
mvp_badge
mvp_label
Posted on 2014-02-26
Justin James
mvp_badge
mvp_label
Rank: #4
Posted on 2014-02-26
Solution
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
Solution
Bob
Posted on 2014-02-26
Bob
Rank: #3540
Posted on 2014-02-26
Solution
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.
Solution
Quick Reply
attachment
Choose File
No file chosen
Submit
Login to reply
New Post
Available Forums
Technology & Integration
News and Announcements
Forge Discussions
Meta
Community Quick Guides
Usability
Installation Troubleshooting
Personal Environment Troubleshooting
Forum Notifications
Email Digest Settings
Loading...