1

Topic: Whether the variant at which one moment of time some flows change the data is possible?

Good afternoon.
Is WEB the server which unit is connected to basis and makes changes to the table-counter. On how many I understand at once time on web the server "heap" of requests can come, and in a flash go set of requests to basis.
There is a stored procedure, for example a script the idle time:

CREATE PROCEDURE [Test]
AS
UPDATE [Test_Table]
SET
[Inc_Fild] = [Inc_Fild] - 1
GO

For example [Inc_Fild] = 100, at once time 100 requests on web the server which which unit began to cause  in hundred different flows came. If not to do BEGIN TRANSACTION, at completion of 100 requests [Inc_Fild] it will be equal "0", or is not present? I to what, whether can be a situation when, the first flow of a procedure call makes changes in a DB and the second reads as though "the dirty" data which changes did not happen yet? Or MSSQL does not allow to make it, and executions of set of flows rises in queue and there will be a performance one behind another?

2

Re: Whether the variant at which one moment of time some flows change the data is possible?

All single requests are fulfilled in implicit transaction

3

Re: Whether the variant at which one moment of time some flows change the data is possible?

_UUS;
It is clear nothing that you ask, but there are such miracles as isolation levels

wrote:

All single requests are fulfilled in implicit transaction

4

Re: Whether the variant at which one moment of time some flows change the data is possible?

sti wrote:

All single requests are fulfilled in implicit transaction

Also what it means?

5

Re: Whether the variant at which one moment of time some flows change the data is possible?

_UUS;
https://ru.wikipedia.org/wiki/__

6

Re: Whether the variant at which one moment of time some flows change the data is possible?

StarikNavy wrote:

Igor_UUS;
https://ru.wikipedia.org/wiki/__

Better do not read, in the core the delirium with a bad advice there is written.

7

Re: Whether the variant at which one moment of time some flows change the data is possible?

Here about what I speak, from article above:
Situation when at simultaneous change of one data package by different transactions one of changes is lost.
Let's assume, there are two transactions executable simultaneously:
Transaction 1 Transaction 2
UPDATE tbl1 SET f2=f2+20 WHERE f1=1; UPDATE tbl1 SET f2=f2+25 WHERE f1=1;
In both transactions field value f2 changes, after their end field value should be increased on 45. Actually there can be a following sequence of actions:
Both transactions simultaneously read a current state of a field. The exact physical simultaneity here is not mandatory, enough, that the read operation second for the order was fulfilled before other transaction writes down the result.
Both transactions calculate new field value, adding, accordingly, 20 and 25 to earlier read value.
Transactions try to write down result of calculations reversely in the field f2. As physically simultaneously two records it is impossible to fulfill, in a reality one of write operations will be fulfilled earlier, another later. Thus the second write operation re-records result of the first.
As a result field value f2 on termination of both transactions can increase not on 45, and on 20 or 25, that is one of transactions changing the data "disappears".
At me a question if not to do BEGIN TRANSACTION and COMMIT TRANSACTION, can happen so, what one transaction can be lost?

8

Re: Whether the variant at which one moment of time some flows change the data is possible?

_UUS;
And if to make two BEGIN TRAN two COMMIT and one SAVE TRAN transaction will be shone a little

9

Re: Whether the variant at which one moment of time some flows change the data is possible?

_UUS wrote:

At me a question if not to do BEGIN TRANSACTION and COMMIT TRANSACTION, can happen so, what one transaction can be lost?

Once again - All single requests are fulfilled in implicit transaction. So all equally do or do not do BEGIN TRANSACTION and COMMIT TRANSACTION.

10

Re: Whether the variant at which one moment of time some flows change the data is possible?

sti wrote:

it is passed...
Once again - All single requests are fulfilled in implicit transaction. So all equally do or do not do BEGIN TRANSACTION and COMMIT TRANSACTION.

The person, can though learn that such implicit transaction before to write "once again"

11

Re: Whether the variant at which one moment of time some flows change the data is possible?

sti wrote:

it is passed...
Once again - All single requests are fulfilled in implicit transaction. So all equally do or do not do BEGIN TRANSACTION and COMMIT TRANSACTION.

Like as is not present. If to do only BEGIN TRANSACTION other transaction cannot change the data which are processed in the first, till the moment of its end

12

Re: Whether the variant at which one moment of time some flows change the data is possible?

TaPaK wrote:

Igor_UUS;
And if to make two BEGIN TRAN two COMMIT and one SAVE TRAN transaction will be shone a little

You and cannot give the distinct answer... Whether "yes" whether "is not present".
How should look TSQL to guarantee what misses or Update'?

13

Re: Whether the variant at which one moment of time some flows change the data is possible?

_UUS wrote:

it is passed...
You and cannot give the distinct answer... Whether "yes" whether "is not present".
How should look TSQL to guarantee what misses or Update'?

I in horror learned that they happen do not get

wrote:

there are such miracles as isolation levels

Read and think

14

Re: Whether the variant at which one moment of time some flows change the data is possible?

_UUS wrote:

As should look TSQL to guarantee what misses or Update'?

Insured should look, for 100 % of a warranty are given only by the insurance policy.

15

Re: Whether the variant at which one moment of time some flows change the data is possible?

UPDATE a set
SET
[Inc_Fild] = [Inc_Fild] - 1
from [Test_Table] a with (xlock, holdlock)

16

Re: Whether the variant at which one moment of time some flows change the data is possible?

Sergey Alekseevich wrote:

it is passed...
The insured should look, for 100 % of a warranty are given only by the insurance policy.

And it does not give

17

Re: Whether the variant at which one moment of time some flows change the data is possible?

_UUS wrote:

Both transactions simultaneously read a current state of a field.

At update it is impossible.
Read about locks. In particular about U.

18

Re: Whether the variant at which one moment of time some flows change the data is possible?

Sergey Alekseevich wrote:

UPDATE a set
SET
[Inc_Fild] = [Inc_Fild] - 1
from [Test_Table] a with (xlock, holdlock)

Thanks, esteemed, similar that that is necessary. But in the description to be said that it is necessary to open is mandatory transaction i.e. somewhere above should be BEGIN TRANSACTION
It so? Or BEGIN TRANSACTION it is possible and not to do?

19

Re: Whether the variant at which one moment of time some flows change the data is possible?

_UUS wrote:

it is passed...
Thanks, esteemed, similar that that is necessary. But in the description to be said that it is necessary to open is mandatory transaction i.e. somewhere above should be BEGIN TRANSACTION
It so? Or BEGIN TRANSACTION it is possible and not to do?

you in essence do not read answers? Wrote all in the first answer:

20

Re: Whether the variant at which one moment of time some flows change the data is possible?

Gavrilenko Sergey Alekseevich;
Really implicit? implicit?

21

Re: Whether the variant at which one moment of time some flows change the data is possible?

For the decision of your problem use insert instead of update.

22

Re: Whether the variant at which one moment of time some flows change the data is possible?

edyaN wrote:

for the decision of your problem use insert instead of update.

So who again in a hell did not close a door?

23

Re: Whether the variant at which one moment of time some flows change the data is possible?

Sergey Alekseevich wrote:

it is passed...
You in essence do not read answers? Wrote all in the first answer:

You tangled me, at what here not explicit transactions??? I do not use generally SET IMPLICIT_TRANSACTIONS ON
I need to address to the table, to block one record c set ID, to make to it changes. At the moment of the record lock, all parallel changes should not be imported to its unblocking. Or to block all table (it is not desirable but if other guaranteed variant is not present, start up will be all)

24

Re: Whether the variant at which one moment of time some flows change the data is possible?

_UUS;
The answer is already given you. If mastered to read about , read and about explicit and  autocommit
https://technet.microsoft.com/en-us/library/ms187878 (v=sql.105).aspx

25

Re: Whether the variant at which one moment of time some flows change the data is possible?

TaPaK;
Yes it is fine, there the data ...
100 in a second = 8640000 a day. For int it  33 mb. Well there the key what-thread, let will be 100 mb. Same not a problem.