Database locking in MM5

[Kent Multer]

Hi folks --

In a MySQL store, what's the equivalent for MvLOCKFILE? I'm working on a module that needs to do some non-interruptible operations, such as incrementing a StoreKey counter. How do you do that with SQL?

Thanks --

[mvmarkus]

Hi folks --

In a MySQL store, what's the equivalent for MvLOCKFILE? I'm working on a module that needs to do some non-interruptible operations, such as incrementing a StoreKey counter. How do you do that with SQL?

Thanks --


Hi Kent,

Probably the easiest way is to wrap all tasks into a single transaction, setting a manual commit in the beginning and at the end use MvCOMMIT - but of course this requires that the table types support it. Other than than, you can send a manual command to the server to (write) lock the table, unfortunately where I am at the moment, I don't have an example handy. If you want I can send you one tomorrow.

A word of caution: I am not entirely sure if I understand the locking mechanism in MySQL 100% correctly - because at several points in their reference manual they write that they apply only to a single thread - leaving the possible option that another thread may still write to it. This makes of course very little sense, but well, I want to mention it anyway.... In great likelihood I am missing something there....

Markus

[Kent Multer]

Hi Markus -- thanks for the reply. I could definitely use a few more details. What I want to do right now is simply create a replacement for the old StoreKey_Generate function. Unless there's a new API function for this that I'm not aware of, I need a way to lock the database while I'm reading it and incrementing one of the counters.

I found a few bits of documentation on MvCOMMIT and MvTRANSACTION, but if you could give me an example, I'd appreciate it. Thanks --

[mvmarkus]

Hi Markus -- thanks for the reply. I could definitely use a few more details. What I want to do right now is simply create a replacement for the old StoreKey_Generate function. Unless there's a new API function for this that I'm not aware of, I need a way to lock the database while I'm reading it and incrementing one of the counters.

I found a few bits of documentation on MvCOMMIT and MvTRANSACTION, but if you could give me an example, I'd appreciate it. Thanks --

Hi Kent,

I really can't help you with the Merchant API, but maybe give you some examples. Funny also that you mention MvTRANSACTION. I completely forgot about that one. Instead I use the following construct:

<MvDBCOMMAND NAME = "schema" COMMAND = "manualcommit">
here some database queries
<MvCOMMIT>

(but I guess that using MvTRANSACTION is the proper way to go...)

Thanks to the MvDBCOMMAND-tag, I assume you can use most MySQL locking and transaction commands directly, such as START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT, ROLLBACK TO SAVEPOINT as well as LOCK TABLES and UNLOCK TABLES.

The MySQL reference sais the following about LOCK TABLES:

LOCK TABLES
tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES

LOCK TABLES locks tables for the current thread. UNLOCK TABLES releases any locks held by the current thread. All tables that are locked by the current thread are implicitly unlocked when the thread issues another LOCK TABLES, or when the connection to the server is closed.

Note: LOCK TABLES is not transaction-safe and implicitly commits any active transactions before attempting to lock the tables.

As of MySQL 4.0.2, to use LOCK TABLES you must have the LOCK TABLES privilege and a SELECT privilege for the involved tables. In MySQL 3.23, you must have SELECT, INSERT, DELETE, and UPDATE privileges for the tables.

In general, LOCK TABLES should not be necessary for what you are looking to accomplish - on the opposite; my gut feeling tells me that it may even be counter productive. Better wrap this into a transaction - after all the purpose of transactions is exactly that all tables are properly updated - or none, in case an error occurs.

The Miva MvLOCKFILE is a fairly useless command in my opinion, unless you ensure that all threads that access the table respect a lock. This is, as far as I know, not the case in Merchant (but I may be wrong).

Markus

[Kent Multer]

Thanks for the info. I just tried MvTRANSACTION, and I got this message from the compiler:

uploadattr.mv:100: CW_8: Tag name 'MvTRANSACTION' may be a misspelled or obsolete Miva Script tag

So go figure ... I guess I'll try your DBCOMMAND approach next.