A customer has a problem where while one user is doing some "horrible batch process" they don't want any other user to carry out "another horrible process". There is the danger that the "horrible batch process" may not complete due to an issue or a network failure, also it cannot (for various reasons) be completed in a single transaction.

Various possible flagging mechanisms for dealing with this all have issues with cleaning up in the case that the connection doing the "horrible batch process" disconnects before completion, which would then require a manual resetting of a flag.

One suggestion was to use lock table a dedicated table with LOCK TABLE and that the second process would not start if it could not successfully carry out an appropriate operation - however because of the transaction issue (see above), the WITH HOLD clause would be needed so that the lock would survive commits. However in the absence of a RELEASE TABLE statement is there a way of releasing such a lock without disconnecting? Just setting a new table lock without the WITH HOLD doesn't seem to do it. Better ideas gratefully received.

In general everything operates at isolation level 0.

v11.0.1

asked 26 Mar '14, 13:05

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%

1

FWIW: In case the two processes would run on the same machine, you could also use OS-specific IPC mechanism like mutexes to synchronize them, that's what we have done for some database-based tasks, too... - the OS will make sure abandoned resources will get freed...

(26 Mar '14, 16:46) Volker Barth

Alternative idea:

You can use the flag scenario in combination with a database timer event. Your process needs to update the flag every minute. The database timer will check every two minutes, if the flag is outdated (older than 1 minute). In this case the database timer is cleaning the flag.

This approach will quarantee, that in case of a failure in the "horrible batch process" the flag will be cleared.

This approach is independent of transactions, connections and even robust towards short network disconnects.

(Any credits for the basic idea go to the team of Microsoft Exchange, don't know where they have borrowed it ;) )

permanent link

answered 26 Mar '14, 13:25

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

Thanks Martin - that's certainly worth trying :)

(26 Mar '14, 13:28) Justin Willey
2

You don't need to have a timer event to 'clean the flag'. Just update the table row with the current timestamp every so often to indicate that the connection doing the 'horrible batch process' is still alive... then any other connection can look at the table and determine if there is any other connection 'working' simply by determining how old the timestamp is. You could also store the connection ID of the connection that is doing the 'horrible batch process' so you / other connections would know which connection is doing the work. You could then also test to determine if the connection (stored in the table) still existed and proceed (skip the timestamp test) if the connection did not exist.


If transaction semantics get in the way (because you have a long transaction on the connection doing 'the horrible batch process'), then you could create an event that will updates the table (with the timestamp/connection id) and have the connection doing the batch process trigger the event every so often and pass the connection ID (as an event parameter) to the event handler.

(26 Mar '14, 16:19) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×21
×12
×4

question asked: 26 Mar '14, 13:05

question was seen: 1,962 times

last updated: 26 Mar '14, 16:46