-2

I'm working on an asp.net web site application that uses ODBC to store data into a 2008 R2 Microsoft SQL Server.

My understanding of ODBC is that it is an open database connection, where the connection to the database can be kept open, and during this time only "one connection" can modify, update, insert into the database. Until that connection is closed and another one is opened.

The application is designed to allow multiple user sessions to open connections to the database at the same time, and I don't see any client code that is handling concurrency issues regarding insertions or modifications to the database.

How are multiple users in separate sessions (for example, three users on three separate web browsers), able to modify, update, and insert information safely into one database; particularly when all three users are modifying, deleting, or inserting into the same row at the same time. Does the database automatically provide a lock and wait for that lock to free up from one user session to the next to resolve this concurrent data access issue?

Tony Ko
  • 81
  • 1
  • 2
  • 14
  • "open" in odbc means that there is documentation and anyone can use it :-) It is/was an open standard, and not a secret protocol. The channel can be left open (Keep Alive) or closed after every command, but it is the client program that choose its strategy – xanatos May 21 '18 at 19:09
  • ahhh okay. that makes sense. So client is in charge of opening, then closing connection. So if client has connection open, another session won't be able to secure an open connection, until the first connection is closed, correct? only one user/session will be allowed to connect to the database until that connection is closed – Tony Ko May 21 '18 at 19:11
  • Why should there be a limitation of 1 connection/user? Try using the SQL Management Studio and opening different connections from the same user... There is no limitation (or if there is we are speaking of big numbers) to the number of connections from one user. It is normal that an SQL Server is accessed from hundred of users (clients) at the same time – xanatos May 21 '18 at 19:13
  • understood. So to go back to what you previously said, the application would need to handle the concurrent data manipulation issue; not the server per say – Tony Ko May 21 '18 at 19:20
  • It is a little more complex. As written by Jiggler, the SQL will return errors if you do for example a SELECT and then in the same transaction an UPDATE of the rows that you SELECTed and at between the SELECT and the UPDATE someone else has modified the data. But it is a complex argument, not something to explain on SO. See for example https://stackoverflow.com/a/129397/613130 – xanatos May 21 '18 at 19:23
  • Then you can read about [TRANSACTION ISOLATION LEVEL](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017)s for SQL Server (those concept work similarly for other databases) – xanatos May 21 '18 at 19:25
  • Thanks xanatos. I'll definitely read up on that. I appreciate you taking the time to answer my questions :) – Tony Ko May 21 '18 at 19:27

2 Answers2

1

You cannot modify the same row on the same table at the same time as someone else. This is inherent in the ODBC connection statement. For instance, UserA is updating Table1 on and UserB is also trying to perform updates on Table1, if there are rows in common between the two users, the ODBC will throw an error with feedback about the data currently being locked or something similiar (depending on how you're accessing the ODBC).

Regardless of Optimistic or Pessimistic style of ODBC record locks, only one user can make modifications to the same data at the same time.

However, multiple users can make changes to the same Table if no conflicts occur in the modifications taking place in certain circumstances (this would be a difference in locking types).

FYI - This is not really a coding question.

ccarpenter32
  • 1,058
  • 2
  • 9
  • 17
  • 1
    Subject... The subject is everything... It isn't the ODBC that will throw an error... it is the SQL Server (or any other sane connected DB) that will return an error that will be passed by the ODBC. The ODBC is only a channel. – xanatos May 21 '18 at 19:20
  • Thank you sir. That makes good sense. Since I dont really see the C# code (website) handling any concurrency issues, only see open connections and close connections in the code. Thank you xanatos as well. I'm concluding it is the ODBC protocol that handles the user concurrency issue – Tony Ko May 21 '18 at 19:26
0

Each connection is isolated. You could have 1000s of connection.

A web server acts on the benefit of many http connections so you don't need a lot.

It is possible to share a connection across threads but it is not a good practice. You could get the response from another session.

The database manages isolation with locks. Only one person can have an update lock at a time.

paparazzo
  • 44,497
  • 23
  • 105
  • 176