footballer2017-08-10 17:17:19
footballer, 2017-08-10 17:17:19

1) questions about locks 2) why is there no example of such a case in the description of isolation levels, which leads to data inconsistency?

Question 1 Here is the following table at the link src-code.net/koncepciya-blokirovok-v-sql-server :
Table 51.4. Isolation Levels and Lock
Duration Isolation Level - Total Lock Duration
Read Uncommited
Read Commited - Held while reading data
Explain what the difference would be between "None" and "Holded while reading data".
Question 2 https://ru.wikipedia.org/wiki/%D0%A3%D1%80%D0%BE%D...
Why, in the description of transaction isolation levels, only the problems of repeated reading of the SAME data (non-repeating read, phantom reading) are described, but they never write about artifacts that can occur when reading DIFFERENT (but interconnected) data at isolation levels below RepeatableRead? Here is an example (you can come up with an example with different tables, but here I will give an example with one table, but different rows that are interdependent):
There is a table of Users with the amounts of their accounts, i.e. the table has 2 columns - UserName and AmountSum. User1 has the amount of 10 rubles, User2 also has the amount of 10 rubles. User1 transfers to User2 5 rubles. The system opens a transaction of the ReadCommitted level, the following scripts are executed inside the transaction:

/*#time1*/Update Users
Set AmountSum = AmountSum + 5
Where UserName = 'Юзер2';
/*#time4*/Update Users
Set AmountSum = AmountSum - 5
Where UserName = 'Юзер1';

At the same time, the system administrator decided to see how much money is in the accounts of User1 and User2. He selected these users in the interface and pressed the "show" button. The system launches a transaction of the ReadCommitted level, inside the transaction a cycle is written that executes a separate select request for each selected user. As a result, 2 such selects will be executed inside the transaction:
/*#time2*/Select *
From Users
Where UserName = 'Юзер1';
/*#time3*/Select *
From Users
Where UserName = 'Юзер2';

/*#time1*/ -...- /*#time4*/ is the designation of the order in which queries are executed. It turns out that:
1) at #time1, the first update adds 5 rubles to User2's account, making the amount of his account = 15 rubles, while the update places an exclusive lock until the end of the transaction on the User2 line.
2) at #time2, the first select reads the amount of the User1 account, it is still equal to 10 rubles, the shared lock is set only for the time of the select, i.e. during #time3 shared-locking User1 is no longer there.
3) on #time3 the second select reads the amount of User2's account, but User2 has an exclusive lock, so the select cannot read the amount of User2 and stops.
4) at #time4, the second update deducts 5 rubles from User1's account, making his account amount = 5 rubles, while the update places an exclusive lock until the end of the transaction on User1's line.
5) at /*#time5*/, the data update transaction is completed, and all locks are removed from Users 1 and 2.
6) finally, after unlocking all the rows, the read transaction can read the amount of User2's account, which it could not read at #time3 due to blocking, the amount of User2's account is 15 rubles.
As a result, in this situation, it turns out that the Admin will see the following amounts of accounts: User1 - 10 rubles, User2 - 15 rubles. The data turned out to be stupidly inconsistent. The strangest thing is that I have not seen such an example in articles about isolation levels in SQL, everywhere only about "non-repeatable reads" (ie inconsistent data) when reading the same data. Although I can hardly imagine why in practice it is necessary to read the same data 2 times. But when reading different, but dependent data, which is real in 99% of cases in transactions, we get an analogue of "non-repeatable reading" (the same inconsistent data), but this case is not written anywhere.
Can anyone explain this?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
Konstantin Tsvetkov, 2017-08-10

Question 1 Click here
And here is the SQL Server Transaction Row Locking and Versioning Guide . Better to read the original.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question