Hi all, I have a db mirroring as mode=sync and we've been having a bit of trouble with large UPDATE statements locking out all other connections until the statements and connections has finished. Does anyone think it will be worth the risk of moving to mode=page to improve performance or this there something else I can do?

SQL Anywhere 11.0.1.2837

asked 22 Feb '16, 10:02

samrae's gravatar image

samrae
2115618
accept rate: 50%


Choosing async or page mode is a big change from sync mode. In other modes than sync, you can loose committed transactions if your primary goes down.

Is your mirror computer as powerful as your primary computer? If not, you may want to upgrade your mirror computer. But even if they are equally powerful machines this problem can still occur.

The undocumented lagtime option may be causing your problem. This option defaults to 60 seconds. If the mirror is more than this number of seconds behind the primary in apply transactions, then the mirror will block the primary to allow it to catch up. This behavior can cause other connections that are attempting to write to the transaction log on the primary to appear to hang or be locked out.

You may be able to workaround this behavior by setting the lagtime option to a higher value, or by setting it to 0 to disable it completely. You can use "-xp partner=(...);auth=abc123;lagtime=0" to disable the lagtime completely.

Note if you use this option, it can cause failover to take much longer. If the mirror is not applying transactions as fast as the primary, then if the primary fails, the mirror needs to apply all transactions before it can take over as the new primary.

permanent link

answered 22 Feb '16, 12:36

Ian%20McHardy's gravatar image

Ian McHardy
3.0k23152
accept rate: 38%

Large single transactions can be problematic for many different features. In this regard database mirroring is just one example of such a technology. Going to an Asynchronous mode may be able to delay the effect a little but will not completely eliminate it. But that would need testing in your test environment to verify that the trade-offs are acceptable to your usage.

You should consider other approaches as well. Scheduling your large transaction runs at the quieter (ie. more off-hours) periods so the impact will be less is one option. Another would be to break up the large transaction into multiple smaller units of work; that way spreading the smaller impacts across a number of steps so that other transactions can have an opportunity to complete 'in between' those and not have to wait as long even when they do block.

permanent link

answered 22 Feb '16, 11:46

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

This is great, guys. Thanks very much. I really don't want to be a in a position to lose transactions but I also can't really go on having large updates lock out all the users. The mirror is a virtual machine (not my choice) and might be under a more strain recently. A major factor could also be that the server applications are using DDE to talk to each other which can lock up and delay the large updates from happening until someone closes some of the stalled processes.

Thanks again, Sam

permanent link

answered 24 Feb '16, 04:56

samrae's gravatar image

samrae
2115618
accept rate: 50%

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:

×53
×28
×3

question asked: 22 Feb '16, 10:02

question was seen: 342 times

last updated: 24 Feb '16, 04:56