SQL - two users reading at the same time in a table

Question

I write in case there is a problem because there are several users reading at the same time in the same sql table, if there is a user that performs writes / reads on a table, and there is another user reading at the same time, there may be some kind of conflict and the user who reads / writes has trouble doing it?

Answer 1

Hello Friend,

In this case, the basic principle of Database enters.
When a user starts reading it takes 'an image' from the database at that moment. And there is what we call isolation.
One user's access is isolated from the other. if another user accesses the same table and makes a recording the first user will not see this change until the second commits to his 'operation'.
I am speaking in a very simple way but the truth is that when a user starts a change in the table only he can make the change until he finishes. It is called Exclusive Lock. The user does an exclusive 'begin' lock and makes the change. then when he finishes he makes the 'commit' and releases it for the other 'users'.
Did you understand? otherwise I’ll explain it better.

Answer 2

I'm afraid that Jefferson's answer is a bit overly simplified. There are a whole lot of ifs and buts here.

There does have to be any conflict at all. If user A reads row 98 at the same time as user B updates B 1901, there is typically no conflict at all. But if there is no index, so that they have to scan the table to find the row, there can be a conflict.

Although that also depends on isolation level and settings. The writer will always want to take an exclusive lock on the row it updates. For the reader on the other hand, there is a lot of variations.

In SQL Server, with all defaults, the reader and writer will block each other if they try to access the same row. But if you activate the database setting READ_COMMITED_SNAPSHOT, they will not. In the default isolation level READ COMMITTED. But if you elevate to REPATABLE READ or SERIALIZABLE, they will.

Answer 3

Hi @Anonymous ,
This issue depends on the isolation level.
When SQL Server is in the default READ COMMITTED isolation level, query in the current transaction cannot read data modified by another transaction that has not yet committed. However, data can still be modified by other transactions between issuing statements within the current transaction.
The REPEATABLE READ isolation level specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
The READ COMMITTED SNAPSHOT isolation level specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. When a data modification (INSERT, UPDATE, DELETE) takes place, the database engine stores the last committed version of each affected row in an internal table on tempdb. When a SELECT comes along, the engine redirects it to see the last known committed version instead of having to wait for the data modification to complete.
When SQL Server is in the SERIALIZABLE isolation level, statements in the current transaction cannot read data that has been modified but not yet committed by other transactions and no other transactions can modify data that has been read by the current transaction until the current transaction completes. In addition, other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
Please refer to SET TRANSACTION ISOLATION LEVEL and SQL Server Isolation Levels : A Series for more details.
Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet