Hi, I am attempting to go through a SQL database, find an encrypted string, decrypt it, and then re-encrypt it with a new passphrase. The problem occurs when I try to write to the database while the Reader is still open.
My code roughly looks like:
SqlConnection sc = new SqlConnection(" Server=" + dbserver + ";Database= " + db + ";Trusted_Conne ction=True;Mult ipleActiveResul tSets=True;);
string qs = "SELECT Page, Revision, Content FROM [" + db + "].[dbo].[" + dbtable + "]";
sc.Open();
SqlCommand command = new SqlCommand(qs, sc);
myReader = command.Execute Reader();
while(myReader. Read())
{
...
qs = "UPDATE [wiki_data_test].[dbo].[PageContent_v2] SET Content=@catCon tent WHERE Page=@thePage AND Revision=@theRe vision";
command = new SqlCommand(qs, sc);
command.Execute NonQuery();
...
}
I get the error "Transactio n was deadlocked on lock generic waitable...". I tried to create a separate connection just for the writes, but I get an error stating that I cannot connect while the reader is open.
1 2946 MSDN says: While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.
Changes made to a result set by another process or thread while data is being read may be visible to the user of the SqlDataReader. However, the precise behavior is timing dependent.
So you can't use the same connection for update.
You are getting the "deadlock" error because your Reader sets a lock on the database. It is probably SHARED lock, as your Reader only "reads" data and other connections can "read" as well, but cannot write. And UPDATE command's lock "changes" during execution. First, when it searches in database for the row to update, it has SHARED lock (because it doesn't know if it'll find anything and may not need to change locks for writing), and then, if it finds anything, the lock type is changed. This may cause a deadlock.
Maybe you should use DataSets and DataTables instead of DataReaders?
What SQL database you're using? MSSQL Server? If so, this http://www.sql-server-performance.co...d-sql-locking/ may help you understand your problem.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: ss |
last post by:
Dear All,
I use ODBC to connect php (installed on linux) to a MS SQL Server 2000
database. I use iODBC and Freetds.
Everything looks fine, until I try to open a multiple connection at
once to the SQL server (I didn't close a connection before I opened a
new one). I've got an error with strange characters :
Warning: SQL error: ä , SQL...
|
by: James Baker |
last post by:
I've been using ASP.NET for a couple years now and I'm trying to fall back
into ASP for a new position I've acquired...and I'm having a bit of trouble
remembering. I'm running to a problem where the basic logic is as follows:
Query
Do While Not rs.EOF
Query based on value in rs
Another query based on value in rs
Loop
|
by: Sergio |
last post by:
Hi everybody,
I have the following scenario. Several computers with shared disk in a
LAN. Each of these computer has a MySQL server that serves several
databases. I have several clients that communicate with a Java process
that I have in each computer to answer queries for a given database
(it connects to the MySQL server). I also have a...
|
by: Chris Breemer |
last post by:
Hi all,
I believe I'm seeing a bug in DB2 UDB 8.1, or in any case
a behaviour different from DB2 UDB 7.1.
I have a small C program, containing a mixture of ESQLC and CLI
calls, that opens two separate connections to the same database.
On connection 1, I insert a row in table ENT1.
On connection 2, I insert a row in table ENT2.
On...
|
by: tilmann |
last post by:
We are developping an application. The application is written in Cobol,
for the database the client can use either a DB2 UDB on Unix systems or
a DB2 V 7 on z/OS. The application itself can run on Windows or z/OS,
if the database is on z/OS, we are using DRDA for the dataaccess for
the GUI parts.
We have to insert and commit messages in a...
| |
by: Yelena Varshal |
last post by:
Hello,
I am using ODBC to access Microsoft Access database. I use just generic ADO
code. I don't have problems opening multiple connections from the same page
on the development server but the same code does not work on the production
server and on my Windows XP workstation running IIS. One connection opens
fine on the production server and...
|
by: Yelena Varshal via AccessMonster.com |
last post by:
Hello,
What are the pre-requisites / conditions for the ability to create
multiple connections to MS ACCESS database and what is the precedence of its
application? adModeShareDenyNone in the code, Exclusive checkbox in the ODBC
source, Tools->Options->Advanced->Shared in the Database Options, what else?
I have a problem when multiple...
|
by: Anil Gupte |
last post by:
This .Net Database stuff is way too complicated. Just to make a Select
statement on an Access database, I have to first get a connection string,
then open a connection to the database, then create an OLEDBConnection, then
an OLEDBDataAdapter, then a Dataset and then actually use the data. I
counted 12 statements before I can actually populate...
|
by: =?Utf-8?B?Vmlua2k=?= |
last post by:
Hello everyone,
I have this code for TCPListenPort. The code works fine, but my manager is
asking me to establish multiple connections to the same port. How can i
acheive that
below is my code
Int32 port = Int32.Parse(ConfigurationManager.AppSettings);
|
by: whitethomas12 |
last post by:
Hi,
I currently have some basic code that allows me to run the tracert
command through VB.NET and it also updates my database based on the
results.
I was wondering if someone can help me find a way that I can used my
code to run tracert on mutiple instances at the same time. The
following is my current code.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |