Can Multiple Users Upload to Myworkbench Sql

• Remove From My Forums

Multiple users read / update table at same fourth dimension

• Question

• How-do-you-do Team,

  Database: SQL Server 2012 Express

  Forepart Stop: VB .Net application

  I have got a PoS awarding which read concluding Transaction id from a Transaction id table and and so once Transaction is committed gets updated with new value. This is all in single connection from vb.net (single method within vb.net)

  At present what I want to confirm is if two users are nigh to commit a Transaction they both volition have a same Transaction id as they both will read at the same time with two diff connections. This can create trouble every bit they both are diff transactions and my transaction table will consist multiple transaction item within aforementioned transaction.

  I hope I am explaining this well. Hope you guys get the indicate I am trying to brand.

  My questions are

  - Has anyone came across same consequence?

  - how does sql server treats data read from 2 diff connections, I hateful does information technology wait for ane connectedness to close the connection before other connection tries to read the same tabular array?

  - How to overcome this problems? What shall I be careful within my vb.net connection / coding.

  Much capeesh your help.

  Sap

Answers

• I have got a PoS awarding which read last Transaction id from a Transaction id table and then in one case Transaction is committed gets updated with new value. This is all in single connection from vb.net (unmarried method within vb.cyberspace)

  SQL server will do it perfectly to avoid whatever data anomaly if nosotros practise non write poor coding.  Its washed past Acrid property.

  When one users commits, it put a lock on row and other user will wait for commencement transaction to commit. Once its commited, lock will be released, and then that 2nd users tin can now get the lock and commit his transaction.This is  default behaviour.

  For further understanding, please read Acid property and how sql server maintain concurrency.

  ---

  Srinivasan

  • Marked as answer past Fri, June 21, 2013 two:04 PM
• • Marked every bit answer by SapHelp Friday, June 21, 2013 ii:04 PM

• Now what I want to confirm is if two users are about to commit a Transaction they both will have a aforementioned Transaction id as they both will read at the same fourth dimension with two unequal connections. This tin create problem as they both are unequal transactions and my transaction tabular array will consist multiple transaction item within same transaction.

  - how does sql server treats data read from two unequal connections, I hateful does it await for one connectedness to shut the connection before other connection tries to read the same table?

  There is concept in sql server called equally Isolation level the locks and duradion of locks and its compatibility depends on isolation level specified in query,If not specified it volition take default isolation level as read committed which is specified in DB it is connecting

  Two users trying to commit data might not take same id what makes you lot think they have aforementioned ID..each transaction is given diff ID...IF the connectedness are within same query a sinlge SPID volition be assigned ..

  Please read http://msdn.microsoft.com/en-us/library/ms189122(five=sql.105).aspx

  You DMV select * from sys.dm_exec_connections

   Select * from sys.dm_exec_sessions

  sp_who2

  To know more about connection from your application

  ---

  Please marker this reply equally the respond or vote every bit helpful, as appropriate, to arrive useful for other readers

  
  

  • Edited by Shanky_621 MVP Friday, June 21, 2013 eleven:12 AM added line
  • Marked every bit respond by SapHelp Friday, June 21, 2013 2:04 PM

• >> Thus even if 2 users are opening two connections, second user will only be able to read data table once the commencement once has closed the connection to that table.

  You didnt went through any of links provided in post ,if so this would non have been comment...two select can read data from table in database at same time every bit both takes shared lock and its uniform.There is nothing much in query you are just truncating and inserting tape...for query analysis TSQL forum wud be skilful place

  Appreciate if you go through all links provided

  Thanks

  ---

  Please mark this reply every bit the respond or vote equally helpful, as appropriate, to make it useful for other readers

  • Marked as respond past SapHelp Fri, June 21, 2013 2:04 PM

• Thus even if two users are opening two connections, 2d user will only be able to read data table once the kickoff one time has closed the connection to that table.

  I am reading the information from the transaction Id table in one line and and so immediately in next line truncating the table and replacing it. Beneath is my vb.net code cake.

  Feel free to Critically evaluate it.

  There are a number of issues with the code simply rather than critique it I think it would exist amend to simply take a step back.  Is the intent here simply to assign 2 incremental transaction ids?  If so, I recollect information technology would be better to utilize a different method that truncate/insert 2 different single row tables.

  I suggest a SEQUENCE so that y'all won't need to worry about the concurrency.  For example:

  CREATE SEQUENCE dbo.SequenceTranId Equally int 	Beginning WITH 1 	Increase By 1 	NO CACHE; CREATE SEQUENCE dbo.SequenceTranZId Every bit int 	START WITH 1 	INCREMENT BY 1 	NO Enshroud;                    

  Sample VB.NET code:

                        getIds = "SELECT Side by side VALUE FOR dbo.SequenceTranId AS TranId, NEXT VALUE FOR dbo.SequenceTranZId AS TranZId;"         Dim SqlCommIds As New Organization.Data.SqlClient.SqlCommand(getIds, sqlConnZ)         resIds = SqlCommIds.ExecuteReader         resIds.Read()         vTranId = resIds.GetInt32(resIds.GetOrdinal("TranId"))         vTranZId = resIds.GetInt32(resIds.GetOrdinal("TranZId"))         resIds.Close()

  
  

  ---

  Dan Guzman, SQL Server MVP, http://www.dbdelta.com

  • Marked as respond past SapHelp Fri, June 21, 2013 2:04 PM

jordanmistne.blogspot.com

Source: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cd123726-eded-429c-a608-a20b478aadbf/multiple-users-read-update-table-at-same-time

Share this post