SQL deadlock issue?

[Kent Multer]

Hi folks --

Recently I've had problems with a couple of custom modules, where routine SQL operations seem to hang the script, or make it run really, really slow. One thing that both occurrences had in common is that the module was trying to modify a table while it had a VIEW open on the same table. Is there any known reason why that would cause some sort of deadlock condition? Anyone else seen anything like this?

Thanks --

[wcw]

By modify a table, do you mean change the structure of just update the values in a record?

[Kent Multer]

In one case, I was updating a field value; in the other, I was deleting records.

[wcw]

Interesting. I have a module where I look up a record so the view contains that one record. Then I update a field in that record. It does not appear to hang or run slow. If this is an issue, I could easily close the view before running the update. Maybe others have a thought on this issue.

[Kent Multer]

In both my cases, I had a loop that opened a view of a lot of records; and within that loop & view, it was modifying or deleting individual records. I was able to fix one module by rearranging it into to loops: one that reads all the records and closes the view, and a second one that does the updates. I'll let you know if the same thing works on the other one.

Thanks --

[wcw]

I think I only did that in one module. I never saw a problem but it was only reading one record, ie where id EQ a l.product_id. After reading your post, I changed mine just to be safe. Thanks for the heads up.

[Kent Multer]

I should also point out that this happens with some stores, but not others. Maybe it's related to a particular version of MySQL, or Apache, or something; sorry, I'm not expert on server config.

[BillBuilt]

Hi. I can't comment on Merchant, but i have had some experiences with MySQL and nested openviews/queries. I have 1 server where i cannot have muliple views opened on the same database alias. Also i cannot run queries (MvQUERY) while there are MvOPENVIEWS open as well. For that server, i have to code everything to happen sequentially unless i open multiple aliases (MvOPEN) on the same database.

Others i have worked on do not matter how many are opened at once. They work like standard DBF calls or as if using ODBC in windows. I have no idea how to make the first work like the latter, but it would make my life much easier if i could.

The laggy behavior is probably due to looping sql errors not being displayed.

[Kent Multer]

I had another occurrence of this problem with another module. Again, this is a module that's been on the market for some months, and is in use by a handful of other stores, and no one else has had a problem. The symptom was that the script would time out. The problem turned out to be that the module had a view open to a particular record, and tried to delete the same record while the view was open. It may be significant that the delete command actually used a field from the view, in the form of:

Open a view "MyDB" on a table.
Position MyDB to a particular record.
Use MvQUERY to DELETE the record from the table, using MyDB.d.record_id in the FIELDS attribute to specify the record to delete.

So maybe the problem is triggered by this "overlap" of using a field at the same time you try to delete it? Still, this is not documentated to be a limitation of Miva Script of MySQL, as far as I know, so it must be a bug, which happens in some stores but not others.