1

Topic: SQL SERVER 2008 sp4 Transaction context in use by another session

All kind time!
Is such :
2 servers And and  to each other moreover and loopback both (physically by different machines). There is an application which adds at first in the table of basis allocated on server A On the table the trigger (after update insert) which adds on a condition in the table on the server B. [b] On the server, on this table is registered as the trigger which of application updates the table hangs And and something Would draw out for something from the table on the server further in the trigger (normal select). here happens such that at some users (not at everything, but occasionally happens), at attempt to update the table of the server the table on the server And (on which the trigger writes that to the table And it is selected fat above) hangs up application and throws out an error Transaction context in use by another session
Prompt , what in such situations it is possible to try to make? About distributed transactions on loopback servers heard, but salt that the error arises no means always.
Thanks!

2

Re: SQL SERVER 2008 sp4 Transaction context in use by another session

the uncle wrote:

All kind time!
Is such :
2 servers And and  to each other moreover and loopback both (physically by different machines). There is an application which adds at first in the table of basis allocated on server A On the table the trigger (after update insert) which adds on a condition in the table on the server B. [b] On the server, on this table is registered as the trigger which of application updates the table hangs And and something Would draw out for something from the table on the server further in the trigger (normal select). here happens such that at some users (not at everything, but occasionally happens), at attempt to update the table of the server the table on the server And (on which the trigger writes that to the table And it is selected fat above) hangs up application and throws out an error Transaction context in use by another session
Prompt , what in such situations it is possible to try to make? About distributed transactions on loopback servers heard, but salt that the error arises no means always.
Thanks!

Look, in what  - one basis updates the second for you, and second first. And where a truth point? What basis is more important? In default bases and  on a backup copy - for what basis it will be absolutely bad, and for what not so?
That is I would begin that defined - during each specific moment of time any information which is in one of bases and which can update something from other basis, it than is controlled - application which explicitly works with this basis, or action of the trigger by which something does of other basis.

3

Re: SQL SERVER 2008 sp4 Transaction context in use by another session

Valentine the uncle;
I will explain the moment. Here there is a table of balance on the basis of A and the table of operations on a card of the client on the basis of B. Perhaps, architecture to alter? Because operations in B went plus of 10 roubles, plus of 10 roubles, a minus of 5 roubles, triggers registered balance 15. Then time - and basis B is destroyed, were rolled away on a state where there is an operation plus of 10 roubles. And the balance that in basis B remained as 15 roubles. I hope, the simplified pattern evident enough?

4

Re: SQL SERVER 2008 sp4 Transaction context in use by another session

Andy_OLAP;
That is the balance of basis A remained. 15 roubles. And in basis B after  there was only a first conducting from three. We received 2 versions of truth. Balance of 15 roubles - truth. Balance of 10 roubles - too truth. And further in basis B 10 roubles still arrives, and there at recalculation you receive 20, and balance of basis A for application which with this basis works, shows 25 roubles.
I would consider triggers as a pistol from which shoot to itself at a foot. Alas.

5

Re: SQL SERVER 2008 sp4 Transaction context in use by another session

Andy_OLAP;
How much I understood you, you mean that it is necessary to alter the code so that to minimize loss of the necessary information.
But I turned code locations which as it seemed to me the most suitable candidates on this error procedure which writes an operating time of code location and accordingly when we will have only one label it will be possible to collect some statistics and to reveal regularity, but an update of record from application in the table on the server which is added from the trigger of the first server, after application hangup completely rolls away transaction and .. It is written nothing to broad gulls about a code operating time. Means it is necessary to turn a update in application, but it is less informative it turns out. There are at you assumptions?

6

Re: SQL SERVER 2008 sp4 Transaction context in use by another session

Valentine the uncle;
I understood your problem, but here it is necessary to look at the code and long to think. It turns out - there is a reference table in basis A, there there is a record, further it partially arrives in basis B the trigger, then application updates in basis B.
Well and being returned for example with balance of the client. We simplify it.
Is incremental id int identity (1,1), we add Ivanov - it becomes 101 because 100 records already are.
In basis B the surname Ivanov and initial balance of 100 roubles arrives id 101.
Application calculates to it a scoring of 5 roubles, the balance becomes 115. And through application it was made by the specific user, he is assured that at Ivanov of 115 roubles.
Further there is a failure in basis A and rollback on a state when Ivanov is not present. Well you will do. Application which works with basis A, adds Petrov and sends on B. Further application on B to basis puts to Petrov a scoring 1 rouble, the balance needs to be installed in 101 rouble (Petrov yet did not benefit anything, and the first scoring are start 100 plus 1 rouble), but in basis B there is digit 115. As it is the user benefited, it is necessary to put more, and it turns out 101 instead of 115. Sentry. And the internal logic of application produces on the screen to the user - I can not write down to Petrov a scoring, at it already 115.
Call the one who to Ivanov the scoring wrote down, and speak "I will not understand that with system, I try id 101 to write down to Petrov a scoring - and in any way", and the user with astonishment "as is Petrov, id 101 it is Ivanov". And Ivanov physically already is not present in bases - , either addresses, or mails.
Understand a deal? Even without double updates and loopback - here is how to process such errors? Therefore I against triggers. And all transactions need to be won back in one basis. That they laid down in one log, between 2 .

7

Re: SQL SERVER 2008 sp4 Transaction context in use by another session

the uncle wrote:

it is necessary to alter the code so that to minimize loss of the necessary information.

Not so. I did not see your code and I do not see a pattern as a whole. But if to include intuition you need not to minimize loss, and to eliminate appearance of TWO versions of the necessary information contradicting each other. Because you then cannot tell - here truth, and here we were simply rolled away in basis and all lost that is necessary.

8

Re: SQL SERVER 2008 sp4 Transaction context in use by another session

Andy_OLAP;
Unfortunately I cannot lay out the code, as the house is not present it)
Now I will try to describe schematically that happens in architecture:
Adding application in the table on server A
The user adds record;
Works the trigger (after insert, update)
For
Looks, whether there is in the table on the server a record with such the identifier:
If is, anything
If is not present to add record in tab.
The check end
Looks, if the update of a certain field from the table trigger on the server, arrived
Updates a certain field leaking (conditionally, to that-that its update launched the trigger) table records on the server
We Would come over to the side
-------------------------------------------------------------------------------------------
C B.
Further, after that, application on the side Would "learn"
About the added record also asks it to update.
The user enters  and pushes the button on which event there is an update.
Here 2 variants: or all normally fulfills, or application, after pushing hangs up
Also throws out such error "Transaction context in use by another session" and rolls away update attempt, well and the trigger on it already described above (the trigger after update).

9

Re: SQL SERVER 2008 sp4 Transaction context in use by another session

the uncle wrote:

adding Application in the table on server A
The user adds record;
....
Further, after that, application on the side Would "learn"
About the added record also asks it to update.
The user enters  and pushes the button on which event there is an update.

I and thought - at you the logician of operation in one application is spaced apart on 2 bases. Re-read my example with conductings and balance of the client above. As you think, if YOU use the similar logic it it is good or bad.
Did not understand, how application on the side Would "learn". Also that Would put in a phrase "on the side".
Once again we return to conductings and balance. Got conducting through application in basis A, further the counted balance (there were 10 roubles, there were 20 roubles) Would arrive in basis, there there are no conductings, and there is a table of current balance.
Further application "learns" that it is possible to work with balance of the user. In B.Smotrit's basis - 20 roubles above boundary 18 roubles the type also puts "good balance" (instead of  "bad"). In basis, correctly?
And further basis A breaks, conducting of 10 roubles disappears, remains only initial 10 roubles in basis A. And tables in basis, where in one place 18, in the friend "good".
Well and.... Two versions of truth. How to run anew all after  bases And, NOT through application, NOT through triggers - at you it is registered somewhere?
Or at you in basis it is conditional only a small cache that type "good balance" then to write down from basis in A.To est's basis basis operative calculation, and basis And eternal storage. But sense then in basis - geographically spaced apart basis on branches also it is necessary to work for you somehow? So do not lean against triggers, and use replication, still something - as though it to express - evident and supervised without dependence from that, the user in application pushed the button or not.