I have a deadlock problem and I am not sure how to solve it.
Sometimes I belive I have done anything I can do to stop them from working on the same data, perhaps it is just Clarion’s way, or SQL server,
I see from logs that we have from 6 to 36 locks during a workday. Yesterday I had 8 locks within an hour. User report that they are kept waiting for browses to fill and windows to open.
Yesterday I had user that apparently read 2.1 billion rows before we managed to kick him out. Impressive, given that no table has more than 3 million rows at the moment.
All deadlocks involve a particular table, one way or another.
This is the structure. Seemed like a good idea at the time (2003).
Most locks happens as a result of changing a status or setting a field on Components, where another browse is asking for lines having data with a particular status.
I use a bitmap on a LONG field to hold multiple statuses, so I toggle a bit when changing status and browses usually filters using field&value>0 .
Also there are locks when adding new records to Components, because that involves adding the ID to yet another table for faster filtering using IN queries.
Looking at SQL server
3700 open cursors
300 open sessions (multiple sessions per workstation, probably one per thread), I think we have around 50 instances of the program connected.
Are there any sql property I could set, anything that perhaps make a browse fail earlier. I really do not have time or skills to make my own database driver.
I am using ABC. Not sure that is relevant, since it is most likely some driver vs sql server issue.
SQL Server = MSSQL.
I was looking at IsolationLevel, and the docs said default was set to the lowest possible, so should give less deadlocks.
Is LogoutIsolationLevel only related to logout?
I do not use specific logout anymore. I belive to remember that this caused more issues than they solved, typically multiple users trying to logout the same stuff, or entire table being locked.
There are 4 universal isolation levels. Consult your database documentation for what is recommended and supported. In general, the higher the isolation level, the less chance for data inconsistencies. Lowering an isolation level typically results in better performance.
This property is valid on all SQL based drivers (except Oracle). The default value is 8.
If you are looking for a Dirty Read…
You also dont say if the relationships are defined in the MS SQL server, or in the Dct. I used to run the relationships in the Dct, and just had primary keys and lookup keys in the MS SQL server. I did create the odd view and stored procedure to maintain performance for some reports, but the tables were pretty isolated.
With your typical ODBC SQL driver, including the MSSQL driver, there are two isolation levels you need to worry about: the “regular” isolation level and the logout isolation level. Default values would be regular level would be read-committed (2), and the logout level would be serializable (8 or 16).
Personally it just seems wrong to do dirty reads – i.e. take uncommitted transactions as “good” values.
In the ABC templates typically there is a logout as part of the update and delete relationmanager methods. You can also wrap your logout around more than one “file” update using the TransactionManager. Just using things out of the box that will mean that things in the clarion dictionary that happen to related files (like cascaded deletes of child files) will all be wrapped in a single logout, but most of the time you will wrpping your logout/commit around a single row change/delete.
One of the things that you might want to look at if you are running into locking problems is the existence of keys to deal with constraints. For example, if you have a constraint that says “my parent_ID must point to a parent that exists”, but you do not have a key set up on parent_ID in the child table, then any delete in the parent table needs to read the whole child table to see if there are any children that might be orphaned by the parent delete.
The other thing you might want to read about is lock escalation. I know it is a thing in MSSQL but it has been so many years since I read about it that I only know enough to be dangerous.
I suspect you are talking about “Locks” and not “Deadlocks” which are a different beast.
What technique are you using to identify which users are locked and which user is causing the lock? Are you able to know what the locking user is doing at the time of the locks?
In my experience 1 user will be causing the lock and they can be identified in MSSQL Management Studio as the Locking user. If you have your login configured in an appropriate way you can identify who that user is exactly, rather than just identifying the workstation ID which is pretty useless.
Many year ago I found with MSSQL that, for some reason, long reports in Clarion would lock out other users and the only solution I found was to prevent users from running those reports during business hours.
Can you tell us if changing a “Status” needs for that Status to be cascaded down to the child records to also update those statuses? In other words, is Status a STRING foreign key value or a Key foreign key value, so that when you change the Status value it updates 1 record and not many?
Thank you all.
I have been using Brent Ozar’s stuff, he has a sp_lock method, that lists deadlock initiator and victims. Some of his methods list the workstation, other does not. I have indeed asked the administrator on site to change login to database to be able to track particular instances, but they won’t do that.
The table in question have all their relationsship on server. No cascade update or delete is done on client side, all happens on server. Deletes are rare though, usually just update.
The field updated may or may not be part of indexes. I have tried to store value only once, so the Status fields (I have several) are only updated for a single record. As I mentioned, these are LONG fields where I change individual bits. That record quite possibly is part of a result set in another browse, or will be after status has changed. Usually we change status to move from one filter to another, so it vanishes from browse 1 and appears in browse 1 on another machine.
As for IsolationLevel, I meant the driver string, not LogoutIsolationLevel.
All tables use the MSSQL driver.
The owner string has format: server,database,username,password.
Other properties, if relevant:
The “Enclose RI code in transaction frame” is Off. I do not have any relational update or delete in the application.
All files are threaded.
Busyhandling is 2
MARS is true.
I’m going to assume we really are talking about deadlocks here, and that in your original post when you say "I see from logs that we have from 6 to 36 locks during a workday. Yesterday I had 8 locks within an hour. " that we really are talking deadlocks, and the MSSQL is telling you you had them and which transaction it decided had to give way.
Browses opening slowly sounds like a totally different problem.
In order to get a deadlock you have to have two transactions both of which are trying to make at least two changes to the database. At this point we don’t have any idea how that could happen. But it sounds like it is not a Clarion thing. From what you have said, “Enclose RI code in transaction frame” is Off, which would mean that even if Clarion was changing a couple of things (like when you updated a row), they would be in different transactions (and you have also said that you never explicitly use Logout).
So, if we’re talking deadlocks, then I think we have to be talking stuff that is happening in the database when you make a change, and my guess would be triggers. As an example, if someone goes in an changes the status on a component to say production has started, and there is a trigger that fires on update that tries to get the total of all of the time and cost components. And someone else if changing a TimeCalculation, and there is a trigger when that happens to that updates a column in the Component table so that it reflects the time calculation change. I would see that sort of scenario as a deadlock waiting to happen. But I don’t know if that is anywhere close to anything you have going on. I’d suggest triggers in the database might be a good place to look…without anything that Clarion is doing that could try and alter more than one row at a time in a transaction, I’d say you have to look at what is happening in the database.
You can also add the Workstation ID (WSID) to your connection string. This can be useful in identifying who is actually causing the lock and subsequently what they are doing at the time of the block…
If you have a login procedure as part of your apps usage you can add the logged in user’s name to the connection string. I’ve found this useful in MSSQL Management Studio to then know exactly know who is causing the LOCK because it shows up on the LOCK screen. Then you can ask them what they were doing that caused the lock.
It seems that the workstation Id is already detected, I find it in many views. If I could put whatever value I like in the wsid property, like current user, that would have been nice
Many of the connections are from terminalserver, so the workstation is “thisterm” and “thatterm”. In those cases I can’t see which user is doing the task.
(Edit: Sorry, too early in the morning for me, see you have mentioned the UserId as well. Didn’t know we could use named properties in the connection string from Clarion?)
Yeah, that’s the beauty of the WSID property, you can send a value from Clarion that makes more sense. (And effectively has no impact on MSSQL apart from displaying on some screens where it is useful to know!)
Redo your Clarion Browse to use a single SQL View (With Nolock) to allow dirty reads. If the user goes to update, fetch the real record. Since the row has to be reget, there’s virtually no downside to allowing dirty reads in the browse.
Changing WSID did not work, but I could set “App” property. I set that to APP and username, so we could find the users and narrow it down.
sp_WhoIsActive was helpful in some instances, but most processes are resolved within milliseconds, although they are many.
Triggers was not a problem, but when asked, it turned out that the customer had created stored procedures that were updating the offending tables. These procedures were called during reporting, probably of the same data some other user were working with.
Some filters are insanely complicated. It seems that there are a lot to be gained from having slightly simpler filters that put let strain on server, and rather use client side filtering for the more complex issues.
During profiling, I found that PROP:SQL wraps statements into a single transaction, so that could indeed lock some records.
Yes, without any logouts the ODBC/MSSQL connection will “auto-commit”. That happens for each statement you send to the server. The statements that clarion sends will almost all single record statements (update/insert/delete one row). But if you have written a PROP:SQL, say to replace a Clarion process, then the PROP:SQL update will be a a single transaction.
Mostly that is a good thing – it’s much faster and either it all happens or none of it happens. In the Clarion version if the process craps out in the middle you would have to try and figure out who was changed and who wasn’t. In the single statement version you still have to try and figure out why the statement didn’t work, but you don’t have any half done stuff to clean up.
But in looking for deadlocks, yes, that is a place where Clarion may have locks on multiple rows at the same time (i.e. everything updated by the one PROP:SQL statement).
