DataSource Controls :: Cannot Read Committed Rows While Another Transaction Is In Process
May 3, 2010
i have a method that do bunch of inserts in a table
these inserts done through a transaction (with default isolation level)
meanwhile i need to read some committed records from this table by using another method, i create another transaction for 2th method with a ReadCommitted isolation level but it just fails and return a timeout error and also, the DoTransaction cause the entire table inaccessible, how could i apply a row-level lock ?
I would like to be able to read the first row of a table and then each successive row sequentially individually. So, is there a way to read the first row of a table, whatever the primary key may be, and then read the next sequential row in the table until the last row has been read? I need to be able to do this in order to create a list of column entries in each row for a listbox control that a user can select from.
I need a way to read all the rows in one column from an Excel file that the user picks from a fileupload control. I tried saving the excel file on the webserver with the upload control and then opening it with a OleDbConnection but that fails when running it from the webserver and gives the error:
Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
This works fine when just running it localhost, so i figured it might be a problem with security level at the webserver which i cant change. Instead i tried doing it with a streamreader direct from the fileupload control and because theres no need for saving the excel file on the server, but im not sure how to query the rows i need this way.
I have a stored procedure which fetches data after joining 8-9 tables and inserts that into a temp table. It was running fine till now, but now when the amount of data fetched have exceeded 20000, the SP is breaking. I have debugged the sp and found that this main query is failing after returning arround 15000-16000 records.
The error message says Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I know what is deadlock, but when i checked with SP_LOCK stored proc i only found that my process is running on database only. So how is this getting deadlocked when there are no other processes running simultaneously on server.
I have a deadlock problem that occurs every 5 minutes on SQL update. I get the following error randomly - means qurey failed one time every 200 calls. I'm using transaction scope to manage transaction.
code:
using(TransactionScope scope = new TransactionScope()) { SELECT ... FROM TABLE1 UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId scope.Complete(); } error: System.Data.SqlClient.SqlException: Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DAL.RunSQL(String query, SqlParameter[] parameters, Boolean openTransact) in C:prjDAL.cs:line 215 I took trace in SQL about deadlock and same SQL query is mentioned bu deadlock. I do not understand how to resolve this deadlock. SQL log: deadlock-list deadlock victim=process5462718 process-list process taskpriority=0 logused=0 waitresource=KEY: 20:72057597472604160 (ce004d0b917a) waittime=30 ownerId=5937428 transactionname=user_transaction lasttranstarted=2010-10-20T16:29:04.050 XDES=0x5d00280 lockMode=X schedulerid=2 kpid=2168 status=suspended spid=65 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2010-10-20T16:29:04.110 lastbatchcompleted=2010-10-20T16:29:04.110 clientapp=.Net SqlClient Data Provider hostname=IPTBEWKS017 hostpid=3112 loginname=iptuserjde isolationlevel=serializable (4) xactid=5937428 currentdb=20 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 executionStack frame procname=adhoc line=1 stmtstart=222 sqlhandle=0x0200000086adfa0c959ccbdd7ef0e31e854e3a987706ae38 UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000 unknown inputbuf (@UId nvarchar(9),@F1 nvarchar(9),@F2 nvarchar(9),@F3 int,@F4 int,@F5 datetime)UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId process taskpriority=0 logused=0 waitresource=KEY: 20:72057597472604160 (cc0078aef293) waittime=88 ownerId=5937412 transactionname=user_transaction lasttranstarted=2010-10-20T16:29:04 XDES=0x2b028280 lockMode=X schedulerid=1 kpid=6788 status=suspended spid=74 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2010-10-20T16:29:04.050 lastbatchcompleted=2010-10-20T16:29:04.050 clientapp=.Net SqlClient Data Provider hostname=IPTBEWKS017 hostpid=3112 loginname=iptuserjde isolationlevel=serializable (4) xactid=5937412 currentdb=20 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 executionStack frame procname=adhoc line=1 stmtstart=222 sqlhandle=0x0200000086adfa0c959ccbdd7ef0e31e854e3a987706ae38 UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000 unknown inputbuf (@UId nvarchar(9),@F1 nvarchar(9),@F2 nvarchar(9),@F3 int,@F4 int,@F5 datetime) UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId resource-list keylock hobtid=72057597472604160 dbid=20 objectname=dbipt_rc41_02.dbo.TABLE1 indexname=PK_TABLE1 mode=RangeS-U associatedObjectId=72057597472604160 owner-list owner id=processdbbe40 mode=RangeS-S waiter-list waiter mode=X requestType=convert keylock hobtid=72057597472604160 dbid=20 objectname=dbipt_rc41_02.dbo.TABLE1 indexname=PK_TABLE10 mode=RangeS-U associatedObjectId=72057597472604160 owner-list owner mode=RangeS-S waiter-list waiter id=processdbbe40 mode=X requestType=convert
I have tried to analyse the trace with this post but unsuccessfully: [URL] UId is my primary key - I do select on F3 column just before in same transaction but i have also an index on F3 column.
-> Spid 65 is running this query (line 2 of proc [p1]): UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId Spid 74 is running this query (line 2 of proc [p2]): UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId
The architecture of the application is straight forward. There is a web application which maintain account holder data. This data is processed and the status of account holders is updated based on number of business rules. This process is initiated using a button on the page and is a long running process (say 15 mins). A component is developed to do this data processing which internally calls stored procedures. Most of the business rules are kept in stored procedure.
I have an invoke function that I call. It On the client side it reads a file and then sends the line to the server side function here and waits for a return. Here is the server side code:
[Code]....
What I find is that I will keep getting these error message as posted in the subject... It will work for some but others I get this message. Thoughts on how to avoid that, or is that just something I'll need to work around with timing?
While updating table Un handled exception was raised (asp.net with c# and SQLServer 2005)
Error Message: "Transaction (Process ID 91) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
I have written several queries under one single transaction. When any of the query throws an error then the whole transaction should rollback. However, in some rare cases say 2% cases even after the error the next transaction is getting comitted.
note that all transaction processes (opening, committing and rollback) is done through .net.
I am using 64bit windows 2003 for data and application server. And website is configured for runapp32on64.
Can I code to add transaction for some subs? If there is an error in any sub will roll back.For example, sub1 delete some tablessub2 insert data into tables which just be deletedsub3 send out confirmation emailsub4 make submit button disable
I have a transaction that has 5 update statements.I am checking that the data is the same as it was when i selected it before i change it, as this will be from a web app that many people could update.
I want to check that the rowcount is 1 everytime i make an update, if it is one then I want to move on, if not I want to rollback and return.
Here is what I have, only it it rolling back on the first attempt everytime.
[Code]....
I am not sure if rowcount is available yet, if not what do I need to use to find out if anyrows are being updated before moving on?
I was facing some serious deadlock issues in a high transaction table.For some select statement i have added nolock .But for few i can not put nolock, as i need accurate data.this select statement becomes a deadlock victim at times.
I code to exec a store procedre (sp_truncate) to truncate 10tables and using sqlBulkCopy to download a huge data. Both them are working fine.Now I want to use transaction with application (not in store procedure) torollback truncate tables if download is failed.Is it possible to use transaction to work with these two steps?
I'm doing a transaction type that is similar to iBank where people can add their payee. In my SQL database, I have a transaction table which has the date variable. I would like to set in a way that if the user did not express interests to activate the payee, the record will be expired after 1hour.
I'm getting "This SqlTransaction has completed; it is no longer usable" exception when try to commit my transaction after sqlreader is close.Here is the code sample
[Code]....
...so when I get to commit the transaction it raises the mentioned exception: "This SqlTransaction has completed; it is no longer usable". I have also noticet that Command.Transaction becomes NULL after reader.Close()My question is: Can I use SqlDataReader and SqlTransaction ? Maybe to use BeginExecuteReader and EndExecuteReader ?
Does a DB transaction get automatically rolled back during exception or we should explicitily write transaction .Rollback inside the catch block to rollback a transaction...
I've got an odd problem and can't figure out what's causing it. We've got 2 different DataSets in the same project and I'm seeing different generated code from the MSDataSetGenerator. Both projects are .NET Framework 4.
In one DataSet, I can open the .Designer.cs file and there's a line like:
[Code]....
This essentially exposes a Transaction property to any partial class I create so I can do with it what I want (by setting it to something).
In the other DataSet no matter what I try to do, this Transaction property never gets generated. I've right-clicked on both DataSets and chose "Run Custom Tool" to get them to regenerate (I've even tried deleting the .Designer.cs file before doing this) and they both get generated the same.
I tried to make sure all the properties of the DataSets were the same. Both are using .NET Framework 4.0 and VS2010.
The file that does not generate the Transaction was from a .NET 1.0 asp.net web site that has continually been upgraded through different .NET versions. Right now it's at 4.0 but I don't know if that would have anything to do with it.
I have one big DataTable with X rows. I want to select Y rows from it and bind them to a new ViewList. While doing it, I want to delete these rows from the DataTable (Having X - Y rows).
What is the best and fast way to do it?
I don't know if it is better to create a new DataTable to have the Y and after that bind them to a ViewList or something else?
I'm also looking for example in code how to select/delete rows from DataTable.
I have a stored procedure which is part of the aspnetdb but i am using my own database and therefore these stored procedures are added on my sql server locally.So i uploaded the databases to my hosting company 1and1 and created the stored procedures and tables as those in aspnetdb but i get the error as in the title. this is a standard stored procedure which lives in the aspnetdb database but not sure why this is happeningthe code is below which is for aspnet membership createuser a standard stored procedure
How do I loop the gridview to read or insert one row by one row?I have a database which has a Primary key. Currently,all the rows seems to appear at one time.Even when I use a counter,it did read one row from a textbox(which is for my testing to read one row) but in the database,it still inserts the whole rows from the gridview.I can't click button twice as it will occur an error "Primary Constraint".
I am developing an application to allow parents to book appointments to meet the child's teachers.
It is compulsory for parents to meet child's mentors, hence Appointments to meet class mentors are already pre-booked in the Appointments table.
Parents could still book appointments to meet other subject teachers. I have a column in my gridview to show parents the list of booked appointments. Parents are allowed to change the Appointment timeslot for an appointment they booked and are not allowed to change appointment time for pre-booked appointments.
Under the Remarks colum, it is stated that the following appointment is pre-booked by class mentor.
How do I make only those pre-booked rows read only since all the appointments belonging to a parent is under one GridView.
I found an example, but this is to only make the 1st 3 rows read-only and I don't think this is in VB coding. I'm usingASP.NET VB coding.
I want to create a flash swf file that will store a token in Local Shared Object. What do I need to do to read that token and look it up in the database on the back end? Is this possible at all? here is a possible scenario:
User visits my web page. A token is generated on the server and stored in the database. Token is then saved in LSO. User leaves the site (maybe shuts down his computer) and comes back later. Token is read, located in the database and the user is recognized (lets say that token is a foreign key that links to other user data stored in the db). I researched this for quite a while and all I find is that flash can use LSO to store read data for flash movie. Does this mean that the data can not be accessed by the server?
