|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
May 4th, 2004, 03:06 PM
|
|||
|
|||
|
Locking problem LCK_M_IX on update
Using Microsoft SQL Server 2000 over ODBC, I have a script wherein I am running into a locking problem. Here's what I'm doing, in pseudocode:
Code:
query = "SELECT keyCol1, col2 FROM t1";
dbConnection1.prepare(query);
dbConnection1.execute();
for each row in (dbConnection1.result()) {
...
query = "UPDATE t1 SET col2='xx' WHERE keyCol1=" + row.keyCol1;
dbConnection2.do(query);
}
My problem is that there is some kind of lock placed by the first query which is prohibiting the second query! The query hangs without any error message. After investigating, the UPDATE query is blocked in trying to acquire a intent-exclusive lock, because a shared lock has been placed by Connection1. How do I select without placing a shared lock? Why can't I modify the record I just read from? I am set to READ COMMITTED transaction isolation level, which shouldn't matter... I have another database on the same server which, when I do the same queries, does NOT place a shared lock when executing the SELECT statements. Are there any database-level settings that I can check to investigate this? P-Sz Last edited by PSzalapski : May 4th, 2004 at 04:22 PM. Reason: incomplete post
|
|
#3
May 8th, 2004, 02:54 PM
|
|||
|
|||
|
It's highly suggested from Microsoft to use OLEDB instead of ODBC direct, OLEDB is just faster and more efficient.
READ COMMITTED is by default from understanding, which causes a LOCK to happen, but why would you want that, when SQL does this automatically? If your locking it, then you need to unlock it right. BTW the language it looks like your using is C++, why are you not using ADO? I don't recognize the properties of your connection object. Is that a database control or something? If so, I highly suggest removing that, because that will only slow everything down and you very limited to what you can do. Quick and dirty as VB example because the code is shorter.. Go to http://www.chizl.com/dev/c++/ for a c++ example on ADO, and using getrows vs looping through the recordset, which is also every slow. sqlStatement = "Select.."; set objRS = objConn.execute(sqlStatement); If not objRS.EOF then vData = objRS.GetRows objRS.Close set objRS = nothing for i = 0 to ubound(vData,2) sqlStatement = "Update"; objConn.execute(sqlStatement); next ALSO.. Your SQL Statement, selecting from the same table your updating too.. SELECT keyCol1, col2 FROM t1 UPDATE t1 SET col2='xx' WHERE keyCol1=" + row.keyCol1 All you have to do is loose the where clause on your update and delete your select statement to do exactly the same thing with No Looping at all. Final result: UPDATE t1 SET col2='xx' Last edited by Chizl : May 8th, 2004 at 02:56 PM.
|
|
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > Sql locking tables on a select cause |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
