sir,
kindly solve the error.When I do a database updation in the
ejbcreate function the driver is working properly but when
I tried to use a user defined function I am thrown an the
exception listed below.This means that teh classpath
settings are fine .Probably the access writes are not
defined properly so I am posting you server.policy file
also just in case there is some problem.
=20
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver
for JDBC]Can't start a
cloned connection while in manual transaction mode.
at
com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source
)
at
com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at
com.microsoft.jdbc.base.BaseConnection.getImplConn ection(Unknown
Sour
ce)
at
com.microsoft.jdbc.base.BaseStatement.setupImplCon nection(Unknown
Sou
rce)
at com.microsoft.jdbc.base.BaseStatement.<init>(Unkno wn Source)
at
com.microsoft.jdbc.base.BasePreparedStatement.<ini t>(Unknown
Source)
at
com.microsoft.jdbc.base.BaseConnection.prepareStat ement(Unknown
Sourc
e)
at
com.microsoft.jdbc.base.BaseConnection.prepareStat ement(Unknown
Sourc
e)
at
com.sun.enterprise.resource.JdbcXAConnection$JdbcC onnection.prepareSt
atement(JdbcXAConnection.java:263)
at Loginsessionbean.checklogin(Loginsessionbean.java: 36)
at
Loginsessionbean_EJBObjectImpl.checklogin(Loginses sionbean_EJBObjectI
mpl.java:19)
at _Loginsessionbean_EJBObjectImpl_Tie._invoke(Unknow n Source)
at
com.sun.corba.ee.internal.POA.GenericPOAServerSC.d ispatchToServant(Ge
nericPOAServerSC.java:423)
at
com.sun.corba.ee.internal.POA.GenericPOAServerSC.i nternalDispatch(Gen
ericPOAServerSC.java:137)
at
com.sun.corba.ee.internal.POA.GenericPOAServerSC.d ispatch(GenericPOAS
erverSC.java:98)
at com.sun.corba.ee.internal.iiop.ORB.process(ORB.jav a:227)
at
com.sun.corba.ee.internal.iiop.LocalClientRequestI mpl.invoke(LocalCli
entRequestImpl.java:90)
at
com.sun.corba.ee.internal.POA.GenericPOAClientSC.i nvoke(GenericPOACli
entSC.java:142)
at
com.sun.corba.ee.internal.POA.GenericPOAClientSC.i nvoke(GenericPOACli
entSC.java:183)
at
org.omg.CORBA.portable.ObjectImpl._invoke(ObjectIm pl.java:297)
at _Loginsessionremote_Stub.checklogin(Unknown Source)
at Loginbean.getLogin(Loginbean.java:34)
at
javaproject.login._0005cjavaproject_0005clogin_000 5clogin_0002ejsplog
in_jsp_1._jspService(_0005cjavaproject_0005clogin_ 0005clogin_0002ejsplogi=
n_jsp_1
..java:140)
at
org.apache.jasper.runtime.HttpJspBase.service(Http JspBase.java:126)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:865)
at
org.apache.jasper.runtime.JspServlet$JspServletWra pper.service(JspSer
vlet.java:161)
at
org.apache.jasper.runtime.JspServlet.serviceJspFil e(JspServlet.java:2
47)
at
org.apache.jasper.runtime.JspServlet.service(JspSe rvlet.java:352)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:865)
at
org.apache.tomcat.core.ServiceInvocationHandler.me thod(ServletWrapper
..java:626)
at
org.apache.tomcat.core.ServletWrapper.handleInvoca tion(ServletWrapper
..java:534)
at
org.apache.tomcat.core.ServletWrapper.handleReques t(ServletWrapper.ja
va:378)
at
org.apache.tomcat.core.Context.handleRequest(Conte xt.java:644)
at
org.apache.tomcat.core.ContextManager.service(Cont extManager.java:440
)
at
org.apache.tomcat.service.http.HttpConnectionHandl er.processConnectio
n(HttpConnectionHandler.java:144)
at
org.apache.tomcat.service.TcpConnectionThread.run( TcpEndpoint.java:31
0)
at java.lang.Thread.run(Thread.java:484)
Loginsessionbean.java
import javax.ejb.*;
import java.util.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import javax.transaction.*;
import java.rmi.RemoteException;
public class Loginsessionbean implements SessionBean
{
private Connection con;
public void ejbCreate() throws CreateException ,
RemoteException ,SQLException , NamingException
{
try
{
InitialContext ic =3D new InitialContext();
DataSource ds =3D
(DataSource)ic.lookup("java:comp/env/jdbc/master");
con =3D ds.getConnection();
}
catch(Exception ex)
{
throw new CreateException(ex.getMessage());
}
}
public String checklogin(String login,String password)
throws RemoteException , SQLException , NamingException ,
CreateException
{
String selectstatement=3D"select * from table1 where login =3D?
and password =3D ?";
ResultSet result;
try
{
PreparedStatement prep =3D con.prepareStatement(selectstatement);
prep.setString(1,login);
prep.setString(2,password);
result =3D prep.executeQuery();
if(result.next()=3D=3D true)
{
prep.close();
return(null);
}
}
catch(SQLException ex)
{
ex.printStackTrace();
}
return "login is correct";
}
public void ejbRemove(){}
public void ejbActivate(){}
public void ejbPassivate(){}
public void setSessionContext(SessionContext session){}
}
server.policy
// Standard extensions get all permissions by default
grant codeBase "file:${java.home}/lib/ext/-" {
permission java.security.AllPermission;
};
grant codeBase "file:${java.home}/../lib/tools.jar" {
permission java.security.AllPermission;
};
grant codeBase "file:${com.sun.enterprise.home}/lib/classes/" {
permission java.security.AllPermission;
};
// Drivers and other system classes should be stored in this
// code base.
grant codeBase "file:${com.sun.enterprise.home}/lib/system/-" {
permission java.security.AllPermission;
};
grant codeBase
"file:${com.sun.enterprise.home}/public_html/-" {
permission java.lang.RuntimePermission "loadLibrary.*";
permission java.lang.RuntimePermission
"accessClassInPackage.*";
permission java.lang.RuntimePermission "queuePrintJob";
permission java.lang.RuntimePermission "modifyThreadGroup";
permission java.io.FilePermission "<<ALL FILES>>",
"read,write";
permission java.net.SocketPermission "*", "connect";
// "standard" properies that can be read by anyone
permission java.util.PropertyPermission "*", "read";
// set the JSSE provider for lazy authentication of app.
clients.
permission java.security.SecurityPermission
"putProviderProperty.JSSE";
permission java.security.SecurityPermission
"insertProvider.JSSE";
};
grant codeBase "file:${com.sun.enterprise.home}/lib/j2ee.jar" {
permission java.security.AllPermission;
};
// default permissions granted to all domains
grant {
permission java.lang.RuntimePermission "queuePrintJob";
// Additional properties needed RI...
permission java.io.FilePermission "*", "read";
permission java.io.FilePermission
"${com.sun.enterprise.home}${file.separator}-", "read";
permission java.io.FilePermission
"${com.sun.enterprise.home}${file.separator}reposi tory${file.separator}-"=
,
"read,write,delete";
permission java.io.FilePermission
"${com.sun.enterprise.home}${file.separator}logs${ file.separator}-",
"read,write,delete";
permission java.io.FilePermission
"${java.io.tmpdir}${file.separator}-", "read,write,delete";
permission java.io.FilePermission
"${user.home}${file.separator}-", "read,write,delete";
// allows anyone to listen on un-privileged ports
permission java.net.SocketPermission "*:0-65535", "connect";
// "standard" properies that can be read by anyone
permission java.util.PropertyPermission "*", "read";
// A version of Merant driver needs this permission.
// permission java.io.FilePermission "<<ALL FILES>>", "read";
// permission java.lang.RuntimePermission "modifyThreadGroup";
};
// permissions granted to all domains
grant {
permission java.lang.RuntimePermission "modifyThread";
permission java.lang.RuntimePermission "modifyThreadGroup";
// DataSource access
permission java.io.FilePermission "<<ALL FILES>>","read,write";
permission java.util.PropertyPermission "java.naming.*",
"read,write";
// Adjust the server host specification for your environment
permission java.net.SocketPermission
"*.microsoft.com:0-65535", "connect";
};
rajat agrawal wrote:
> sir,
> kindly solve the error.When I do a database updation in the
> ejbcreate function the driver is working properly but when
> I tried to use a user defined function I am thrown an the
> exception listed below.This means that teh classpath
> settings are fine .Probably the access writes are not
> defined properly so I am posting you server.policy file
> also just in case there is some problem.
>
Hi. Add a property to the jdbc connection request: selectMethod=cursor
Joe Weinstein at BEA
>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver
> for JDBC]Can't start a
> cloned connection while in manual transaction mode.
> at
> com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
> Source
> )
> at
> com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseConnection.getImplConn ection(Unknown
> Sour
> ce)
> at
> com.microsoft.jdbc.base.BaseStatement.setupImplCon nection(Unknown
> Sou
> rce)
> at com.microsoft.jdbc.base.BaseStatement.<init>(Unkno wn Source)
> at
> com.microsoft.jdbc.base.BasePreparedStatement.<ini t>(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseConnection.prepareStat ement(Unknown
> Sourc
> e)
> at
> com.microsoft.jdbc.base.BaseConnection.prepareStat ement(Unknown
> Sourc
> e)
> at
> com.sun.enterprise.resource.JdbcXAConnection$JdbcC onnection.prepareSt
> atement(JdbcXAConnection.java:263)
> at Loginsessionbean.checklogin(Loginsessionbean.java: 36)
> at
> Loginsessionbean_EJBObjectImpl.checklogin(Loginses sionbean_EJBObjectI
> mpl.java:19)
> at _Loginsessionbean_EJBObjectImpl_Tie._invoke(Unknow n Source)
> at
> com.sun.corba.ee.internal.POA.GenericPOAServerSC.d ispatchToServant(Ge
> nericPOAServerSC.java:423)
> at
> com.sun.corba.ee.internal.POA.GenericPOAServerSC.i nternalDispatch(Gen
> ericPOAServerSC.java:137)
> at
> com.sun.corba.ee.internal.POA.GenericPOAServerSC.d ispatch(GenericPOAS
> erverSC.java:98)
> at com.sun.corba.ee.internal.iiop.ORB.process(ORB.jav a:227)
> at
> com.sun.corba.ee.internal.iiop.LocalClientRequestI mpl.invoke(LocalCli
> entRequestImpl.java:90)
> at
> com.sun.corba.ee.internal.POA.GenericPOAClientSC.i nvoke(GenericPOACli
> entSC.java:142)
> at
> com.sun.corba.ee.internal.POA.GenericPOAClientSC.i nvoke(GenericPOACli
> entSC.java:183)
> at
> org.omg.CORBA.portable.ObjectImpl._invoke(ObjectIm pl.java:297)
> at _Loginsessionremote_Stub.checklogin(Unknown Source)
> at Loginbean.getLogin(Loginbean.java:34)
> at
> javaproject.login._0005cjavaproject_0005clogin_000 5clogin_0002ejsplog
> in_jsp_1._jspService(_0005cjavaproject_0005clogin_ 0005clogin_0002ejsplogin_jsp_1
> .java:140)
> at
> org.apache.jasper.runtime.HttpJspBase.service(Http JspBase.java:126)
> at javax.servlet.http.HttpServlet.service(HttpServlet .java:865)
> at
> org.apache.jasper.runtime.JspServlet$JspServletWra pper.service(JspSer
> vlet.java:161)
> at
> org.apache.jasper.runtime.JspServlet.serviceJspFil e(JspServlet.java:2
> 47)
> at
> org.apache.jasper.runtime.JspServlet.service(JspSe rvlet.java:352)
> at javax.servlet.http.HttpServlet.service(HttpServlet .java:865)
> at
> org.apache.tomcat.core.ServiceInvocationHandler.me thod(ServletWrapper
> .java:626)
> at
> org.apache.tomcat.core.ServletWrapper.handleInvoca tion(ServletWrapper
> .java:534)
> at
> org.apache.tomcat.core.ServletWrapper.handleReques t(ServletWrapper.ja
> va:378)
> at
> org.apache.tomcat.core.Context.handleRequest(Conte xt.java:644)
> at
> org.apache.tomcat.core.ContextManager.service(Cont extManager.java:440
> )
> at
> org.apache.tomcat.service.http.HttpConnectionHandl er.processConnectio
> n(HttpConnectionHandler.java:144)
> at
> org.apache.tomcat.service.TcpConnectionThread.run( TcpEndpoint.java:31
> 0)
> at java.lang.Thread.run(Thread.java:484)
>
> Loginsessionbean.java
> import javax.ejb.*;
> import java.util.*;
> import java.sql.*;
> import javax.sql.*;
> import javax.naming.*;
> import javax.transaction.*;
> import java.rmi.RemoteException;
>
> public class Loginsessionbean implements SessionBean
> {
> private Connection con;
> public void ejbCreate() throws CreateException ,
> RemoteException ,SQLException , NamingException
> {
> try
> {
> InitialContext ic = new InitialContext();
> DataSource ds =
> (DataSource)ic.lookup("java:comp/env/jdbc/master");
> con = ds.getConnection();
> }
> catch(Exception ex)
> {
> throw new CreateException(ex.getMessage());
> }
> }
> public String checklogin(String login,String password)
> throws RemoteException , SQLException , NamingException ,
> CreateException
> {
> String selectstatement="select * from table1 where login =?
> and password = ?";
> ResultSet result;
> try
> {
> PreparedStatement prep = con.prepareStatement(selectstatement);
> prep.setString(1,login);
> prep.setString(2,password);
> result = prep.executeQuery();
> if(result.next()== true)
> {
> prep.close();
> return(null);
> }
> }
> catch(SQLException ex)
> {
> ex.printStackTrace();
> }
> return "login is correct";
> }
> public void ejbRemove(){}
> public void ejbActivate(){}
> public void ejbPassivate(){}
> public void setSessionContext(SessionContext session){}
> }
>
> server.policy
>
> // Standard extensions get all permissions by default
> grant codeBase "file:${java.home}/lib/ext/-" {
> permission java.security.AllPermission;
> };
> grant codeBase "file:${java.home}/../lib/tools.jar" {
> permission java.security.AllPermission;
> };
> grant codeBase "file:${com.sun.enterprise.home}/lib/classes/" {
> permission java.security.AllPermission;
> };
> // Drivers and other system classes should be stored in this
> // code base.
> grant codeBase "file:${com.sun.enterprise.home}/lib/system/-" {
> permission java.security.AllPermission;
> };
> grant codeBase
> "file:${com.sun.enterprise.home}/public_html/-" {
> permission java.lang.RuntimePermission "loadLibrary.*";
> permission java.lang.RuntimePermission
> "accessClassInPackage.*";
> permission java.lang.RuntimePermission "queuePrintJob";
> permission java.lang.RuntimePermission "modifyThreadGroup";
> permission java.io.FilePermission "<<ALL FILES>>",
> "read,write";
> permission java.net.SocketPermission "*", "connect";
> // "standard" properies that can be read by anyone
> permission java.util.PropertyPermission "*", "read";
> // set the JSSE provider for lazy authentication of app.
> clients.
> permission java.security.SecurityPermission
> "putProviderProperty.JSSE";
> permission java.security.SecurityPermission
> "insertProvider.JSSE";
> };
> grant codeBase "file:${com.sun.enterprise.home}/lib/j2ee.jar" {
> permission java.security.AllPermission;
> };
> // default permissions granted to all domains
> grant {
> permission java.lang.RuntimePermission "queuePrintJob";
> // Additional properties needed RI...
> permission java.io.FilePermission "*", "read";
> permission java.io.FilePermission
> "${com.sun.enterprise.home}${file.separator}-", "read";
> permission java.io.FilePermission
> "${com.sun.enterprise.home}${file.separator}reposi tory${file.separator}-",
> "read,write,delete";
> permission java.io.FilePermission
> "${com.sun.enterprise.home}${file.separator}logs${ file.separator}-",
> "read,write,delete";
> permission java.io.FilePermission
> "${java.io.tmpdir}${file.separator}-", "read,write,delete";
> permission java.io.FilePermission
> "${user.home}${file.separator}-", "read,write,delete";
> // allows anyone to listen on un-privileged ports
> permission java.net.SocketPermission "*:0-65535", "connect";
> // "standard" properies that can be read by anyone
> permission java.util.PropertyPermission "*", "read";
> // A version of Merant driver needs this permission.
> // permission java.io.FilePermission "<<ALL FILES>>", "read";
> // permission java.lang.RuntimePermission "modifyThreadGroup";
> };
> // permissions granted to all domains
> grant {
> permission java.lang.RuntimePermission "modifyThread";
> permission java.lang.RuntimePermission "modifyThreadGroup";
> // DataSource access
> permission java.io.FilePermission "<<ALL FILES>>","read,write";
> permission java.util.PropertyPermission "java.naming.*",
> "read,write";
> // Adjust the server host specification for your environment
> permission java.net.SocketPermission
> "*.microsoft.com:0-65535", "connect";
> };
|||Please see the below KB for detailed info:
313181 PRB: Cannot Start a Cloned Connection While in Manual Transaction
Mode
http://support.microsoft.com/?id=313181
Friday, March 30, 2012
J# using JDBC for SQL Server
code:
/* Establish a test connection to remote SQLServer in J# using JDBC */
package JSharpConnTest;
import java.sql.*;
public class JSharpConnTest
{
private static ResultSet rs;
private static Connection conn = null;
private static Statement stmt = null;
private static final String sSubprotocol = "jdbc:microsoft:sqlserver://";
private static final String sServerName = "CLUSTERTEST";
private static final String sPortNumber = "1433";
private static final String sDBName = "ClientLetterWorkRequests";
private static final String sUserName = "sa";
private static final String sPassword = "1111";
private static final String sSQLQuery = "SELECT * FROM CLDatabases";
private static final String sURL = sSubprotocol + sServerName +
":" + sPortNumber + ";
databaseName=" + sDBName + ";
";
public static void main(String[] args)
{
getConnection();
}
public static void getConnection()
{
System.out.println( "Connecting to.. " + sURL );
try
{
//Register the driver
// Microsoft SQL Server 2000 Driver for JDBC
// --problem locating driver?
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
// Pass connection URL
conn = DriverManager.getConnection(sURL, sUserName, sPassword);
// Query database
stmt = conn.createStatement();
rs = stmt.executeQuery(sSQLQuery);
// Test data retrieval
while (rs.next());
{
String colOne = rs.getString("DATABASEKEY");
String colTwo = rs.getString("SERVERTYPE");
System.out.println(colOne + " " + colTwo);
}
}
catch (java.lang.ClassNotFoundException ex)
{
System.err.println("\nClassNotFoundException: " + ex.getMessage());
}
catch (SQLException ex)
{
System.err.println("\nSQLException: " + ex.getMessage());
}
catch (Exception ex)
{
System.err.println("\nException: " + ex.getMessage());
}
finally
{
// Release resources
try
{
if (conn != null)
conn.close();
if (stmt != null)
stmt.close();
}
catch (Exception ex)
{
System.err.println("\nException: " + ex.getMessage());
}
}
} // end getConnection()
} // end JSharpConnTest
This statement:
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Causes the following exception:
[java.lang.ClassNotFoundException]{
"com.microsoft.jdbc.sqlserver.SQLServerDriver"}java.lang.ClassNotFoundExcep
tion
I installed Microsoft SQL Server 2000 Driver for JDBC
I believe the problem is with locating the driver, unlike Java, J# doesn't
use classpath enviromental variable. I am not sure how to add the driver
reference in J#.Hi
Why use JDBC when ADO.NET can do everything for you?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RobertStout" <
RobertStout@.discussions.microsoft.com>
wrote in message
news:4FB06586-4C07-4886-A9E5-444A85DA4F31@.microsoft.com...
>
code:
>
/* Establish a test connection to remote SQLServer in J# using JDBC */
>
>
package JSharpConnTest;
>
import java.sql.*;
>
>
public class JSharpConnTest
>
{
>
private static ResultSet rs;
>
private static Connection conn = null;
>
private static Statement stmt = null;
>
private static final String sSubprotocol = "jdbc:microsoft:sqlserver://";
>
private static final String sServerName = "CLUSTERTEST";
>
private static final String sPortNumber = "1433";
>
private static final String sDBName = "ClientLetterWorkRequests";
>
private static final String sUserName = "sa";
>
private static final String sPassword = "1111";
>
private static final String sSQLQuery = "SELECT * FROM CLDatabases";
>
private static final String sURL = sSubprotocol + sServerName +
>
":" + sPortNumber + ";
databaseName=" + sDBName + ";
";
>
>
public static void main(String[] args)
>
{
>
getConnection();
>
}
>
>
public static void getConnection()
>
{
>
System.out.println( "Connecting to.. " + sURL );
>
>
try
>
{
>
//Register the driver
>
// Microsoft SQL Server 2000 Driver for JDBC
>
// --problem locating driver?
>
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
>
>
// Pass connection URL
>
conn = DriverManager.getConnection(sURL, sUserName, sPassword);
>
>
// Query database
>
stmt = conn.createStatement();
>
rs = stmt.executeQuery(sSQLQuery);
>
>
// Test data retrieval
>
while (rs.next());
>
{
>
String colOne = rs.getString("DATABASEKEY");
>
String colTwo = rs.getString("SERVERTYPE");
>
System.out.println(colOne + " " + colTwo);
>
}
>
}
>
catch (java.lang.ClassNotFoundException ex)
>
{
>
System.err.println("\nClassNotFoundException: " + ex.getMessage());
>
}
>
catch (SQLException ex)
>
{
>
System.err.println("\nSQLException: " + ex.getMessage());
>
}
>
catch (Exception ex)
>
{
>
System.err.println("\nException: " + ex.getMessage());
>
}
>
finally
>
{
>
// Release resources
>
try
>
{
>
if (conn != null)
>
conn.close();
>
if (stmt != null)
>
stmt.close();
>
}
>
catch (Exception ex)
>
{
>
System.err.println("\nException: " + ex.getMessage());
>
}
>
}
>
>
>
} // end getConnection()
>
} // end JSharpConnTest
>
>
>
This statement:
>
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
>
>
Causes the following exception:
>
[java.lang.ClassNotFoundException]{
"com.microsoft.jdbc.sqlserver.SQLServerDr
iver"}java.lang.ClassNotFoundException
>
>
I installed Microsoft SQL Server 2000 Driver for JDBC
>
I believe the problem is with locating the driver, unlike Java, J# doesn't
>
use classpath enviromental variable. I am not sure how to add the driver
>
reference in J#.|||If I could use ADO.NET I would of been done with the entire app a long time
ago.
Sadly, I have to use JDBC.
Can you help?
Thanks
-Rob
I would much rather use ADO.NET but I can't, I have to use JDBC.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Why use JDBC when ADO.NET can do everything for you?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "RobertStout" <RobertStout@.discussions.microsoft.com> wrote in message
> news:4FB06586-4C07-4886-A9E5-444A85DA4F31@.microsoft.com...
> [java.lang.ClassNotFoundException]{"com.microsoft.jdbc.sqlserver.SQLServerDr
> iver"}java.lang.ClassNotFoundException
>
>sql
izzit got for loop in transact-sql stored procedure
Dear all,
i was confuse that izzit there are sql server was support for loop
statement,
i got one section of inner query in my asp program, and i would like
to change in to stored procedure,i using ms sql server as my database.
the following is the asp code with the inner sql
For i = 1 To 10
If not fixQuote(Request.Form("txtDescription" & i)) = "" Then
strSQL = "INSERT INTO ack_item
(Ack_id,Part_no,Serial_no,Description,Qt
y,Itm) VALUES ("& strAckID
&",'"& fixQuote(Request.Form("txtPartNo" & i)) &"',"
strSQL = strSQL & "'"& fixQuote(Request.Form("txtSerialNo" & i))
&"','"& fixQuote(Request.Form("txtDescription" & i)) &"',"&
CheckComma(Request.Form("txtQty" & i)) &","
strSQL = strSQL & "'"& fixQuote(Request.Form("txtItm" & i)) &"')"
call SetConnection(strSQL,2)
End If
Next
could i change whole code into the sql server (store procedure)?Hi
Yep , there is a WHILE loop in the SQL Server
DECLARE @.i INT
SET @.i=1
WHILE @.i<=10
BEGIN
--Do something here
SET @.i=@.i+1
END
Can you elaborate a little bit what you are doing so we can suggest a
solution without using a loop?
<yokesanhoo@.gmail.com> wrote in message
news:1140499360.016330.300370@.g44g2000cwa.googlegroups.com...
> Dear all,
> i was confuse that izzit there are sql server was support for loop
> statement,
> i got one section of inner query in my asp program, and i would like
> to change in to stored procedure,i using ms sql server as my database.
> the following is the asp code with the inner sql
> For i = 1 To 10
> If not fixQuote(Request.Form("txtDescription" & i)) = "" Then
> strSQL = "INSERT INTO ack_item
> (Ack_id,Part_no,Serial_no,Description,Qt
y,Itm) VALUES ("& strAckID
> &",'"& fixQuote(Request.Form("txtPartNo" & i)) &"',"
> strSQL = strSQL & "'"& fixQuote(Request.Form("txtSerialNo" & i))
> &"','"& fixQuote(Request.Form("txtDescription" & i)) &"',"&
> CheckComma(Request.Form("txtQty" & i)) &","
> strSQL = strSQL & "'"& fixQuote(Request.Form("txtItm" & i)) &"')"
> call SetConnection(strSQL,2)
> End If
> Next
> could i change whole code into the sql server (store procedure)?
>
i was confuse that izzit there are sql server was support for loop
statement,
i got one section of inner query in my asp program, and i would like
to change in to stored procedure,i using ms sql server as my database.
the following is the asp code with the inner sql
For i = 1 To 10
If not fixQuote(Request.Form("txtDescription" & i)) = "" Then
strSQL = "INSERT INTO ack_item
(Ack_id,Part_no,Serial_no,Description,Qt
y,Itm) VALUES ("& strAckID
&",'"& fixQuote(Request.Form("txtPartNo" & i)) &"',"
strSQL = strSQL & "'"& fixQuote(Request.Form("txtSerialNo" & i))
&"','"& fixQuote(Request.Form("txtDescription" & i)) &"',"&
CheckComma(Request.Form("txtQty" & i)) &","
strSQL = strSQL & "'"& fixQuote(Request.Form("txtItm" & i)) &"')"
call SetConnection(strSQL,2)
End If
Next
could i change whole code into the sql server (store procedure)?Hi
Yep , there is a WHILE loop in the SQL Server
DECLARE @.i INT
SET @.i=1
WHILE @.i<=10
BEGIN
--Do something here
SET @.i=@.i+1
END
Can you elaborate a little bit what you are doing so we can suggest a
solution without using a loop?
<yokesanhoo@.gmail.com> wrote in message
news:1140499360.016330.300370@.g44g2000cwa.googlegroups.com...
> Dear all,
> i was confuse that izzit there are sql server was support for loop
> statement,
> i got one section of inner query in my asp program, and i would like
> to change in to stored procedure,i using ms sql server as my database.
> the following is the asp code with the inner sql
> For i = 1 To 10
> If not fixQuote(Request.Form("txtDescription" & i)) = "" Then
> strSQL = "INSERT INTO ack_item
> (Ack_id,Part_no,Serial_no,Description,Qt
y,Itm) VALUES ("& strAckID
> &",'"& fixQuote(Request.Form("txtPartNo" & i)) &"',"
> strSQL = strSQL & "'"& fixQuote(Request.Form("txtSerialNo" & i))
> &"','"& fixQuote(Request.Form("txtDescription" & i)) &"',"&
> CheckComma(Request.Form("txtQty" & i)) &","
> strSQL = strSQL & "'"& fixQuote(Request.Form("txtItm" & i)) &"')"
> call SetConnection(strSQL,2)
> End If
> Next
> could i change whole code into the sql server (store procedure)?
>
IX locks question
From what I'm getting, IX locks are just a safety mechanism to allow a
query
to lock at a higher grain (i.e. say, a table lock) if the lock manager needs
to
escalate row locks to page or table locks. Is this a correct assumption?
So, everytime I perform a DELETE or UPDATE can I expect that I will get
X locks on the rows affected and IX locks on the pages and on the table?
Is there a sample example against pubs or Northwind somewhere online that
illustrates this?To state it another way, an IX lock means that an exclusive lock may be held
at a lower level. For example, a row level exclusive lock will also acquire
a table level IX lock. The IX lock will prevent a conflicting table-level
lock from being acquired without having to check individual locks that are
lower in the hierarchy.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.co
mcast.com...
> From what I'm getting, IX locks are just a safety mechanism to allow a
> query
> to lock at a higher grain (i.e. say, a table lock) if the lock manager
> needs
> to
> escalate row locks to page or table locks. Is this a correct assumption?
> So, everytime I perform a DELETE or UPDATE can I expect that I will get
> X locks on the rows affected and IX locks on the pages and on the table?
> Is there a sample example against pubs or Northwind somewhere online that
> illustrates this?
>|||I'm sorry Dan, I'm having trouble understanding. Thanks for bearing with
me.
Is IX lock a safety mechanism of the lock manager?
This is what I think occurs based on my reading and toying around with
pubs...
please correct me where I'm wrong.
Process 1 deletes a row from the authors table. An X lock is placed on the
row being
deleted. IX is applied at the page and the table levels to signal to the
lock manager that there's a lower
level lock because something's going on at a page or row level.
Now, Process 2 comes along to delete rows from the authors table. the lock
manager says "Hold on, there
are lower level row locks that must be checked before you may proceed". At
that point, it checks
to see if the rows it's deleting will cause a page or table lock. If so,
Process 2 will wait because
Process 1 already has an IX lock. If no page or table lock will be needed,
Process 2 will place
X locks on the rows affected and also place it's own IX lock at the page and
table level.
Am I close?
TIA
Dave
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> To state it another way, an IX lock means that an exclusive lock may be
held
> at a lower level. For example, a row level exclusive lock will also
acquire
> a table level IX lock. The IX lock will prevent a conflicting table-level
> lock from being acquired without having to check individual locks that are
> lower in the hierarchy.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.co
mcast.com...
assumption?[vbcol=seagreen]
that[vbcol=seagreen]
>|||> Am I close?
You are correct in your description of Process 1 but it's probably better to
think about Process 2 in terms of lock escalation.
When Process 2 deletes a row, the IX locks are successfully acquired because
these are compatible with Process 1's existing IX locks. Process 2 gets the
exclusive lock on the row to be because it's on a different row than Process
1 is deleting.
When process 2 deletes a lot more rows, SQL Server will try to convert those
many row locks to a single table X lock. However, because that table-level
X lock isn't compatible with the existing Process 1 table IX lock, Process 2
waits until the lock is released.
You can read more about lock escalation in the SQL 2000 Books Online
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_con_7a_5ovi.htm).
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:Fo2dnWYuysxYL2zZnZ2dnUVZ_tmdnZ2d@.co
mcast.com...
> I'm sorry Dan, I'm having trouble understanding. Thanks for bearing with
> me.
> Is IX lock a safety mechanism of the lock manager?
> This is what I think occurs based on my reading and toying around with
> pubs...
> please correct me where I'm wrong.
> Process 1 deletes a row from the authors table. An X lock is placed on
> the
> row being
> deleted. IX is applied at the page and the table levels to signal to the
> lock manager that there's a lower
> level lock because something's going on at a page or row level.
> Now, Process 2 comes along to delete rows from the authors table. the
> lock
> manager says "Hold on, there
> are lower level row locks that must be checked before you may proceed".
> At
> that point, it checks
> to see if the rows it's deleting will cause a page or table lock. If so,
> Process 2 will wait because
> Process 1 already has an IX lock. If no page or table lock will be
> needed,
> Process 2 will place
> X locks on the rows affected and also place it's own IX lock at the page
> and
> table level.
> Am I close?
> TIA
> Dave
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> held
> acquire
> assumption?
> that
>|||Thank you, thank you! That's what I'm looking for!
I'm a visual learner.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eLLSBYfyGHA.1304@.TK2MSFTNGP05.phx.gbl...
> You are correct in your description of Process 1 but it's probably better
to
> think about Process 2 in terms of lock escalation.
> When Process 2 deletes a row, the IX locks are successfully acquired
because
> these are compatible with Process 1's existing IX locks. Process 2 gets
the
> exclusive lock on the row to be because it's on a different row than
Process
> 1 is deleting.
> When process 2 deletes a lot more rows, SQL Server will try to convert
those
> many row locks to a single table X lock. However, because that
table-level
> X lock isn't compatible with the existing Process 1 table IX lock, Process
2
> waits until the lock is released.
> You can read more about lock escalation in the SQL 2000 Books Online
>
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_con_7a_5ovi.htm).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:Fo2dnWYuysxYL2zZnZ2dnUVZ_tmdnZ2d@.co
mcast.com...
with[vbcol=seagreen]
the[vbcol=seagreen]
so,[vbcol=seagreen]
a[vbcol=seagreen]
manager[vbcol=seagreen]
get[vbcol=seagreen]
>
query
to lock at a higher grain (i.e. say, a table lock) if the lock manager needs
to
escalate row locks to page or table locks. Is this a correct assumption?
So, everytime I perform a DELETE or UPDATE can I expect that I will get
X locks on the rows affected and IX locks on the pages and on the table?
Is there a sample example against pubs or Northwind somewhere online that
illustrates this?To state it another way, an IX lock means that an exclusive lock may be held
at a lower level. For example, a row level exclusive lock will also acquire
a table level IX lock. The IX lock will prevent a conflicting table-level
lock from being acquired without having to check individual locks that are
lower in the hierarchy.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.co
mcast.com...
> From what I'm getting, IX locks are just a safety mechanism to allow a
> query
> to lock at a higher grain (i.e. say, a table lock) if the lock manager
> needs
> to
> escalate row locks to page or table locks. Is this a correct assumption?
> So, everytime I perform a DELETE or UPDATE can I expect that I will get
> X locks on the rows affected and IX locks on the pages and on the table?
> Is there a sample example against pubs or Northwind somewhere online that
> illustrates this?
>|||I'm sorry Dan, I'm having trouble understanding. Thanks for bearing with
me.
Is IX lock a safety mechanism of the lock manager?
This is what I think occurs based on my reading and toying around with
pubs...
please correct me where I'm wrong.
Process 1 deletes a row from the authors table. An X lock is placed on the
row being
deleted. IX is applied at the page and the table levels to signal to the
lock manager that there's a lower
level lock because something's going on at a page or row level.
Now, Process 2 comes along to delete rows from the authors table. the lock
manager says "Hold on, there
are lower level row locks that must be checked before you may proceed". At
that point, it checks
to see if the rows it's deleting will cause a page or table lock. If so,
Process 2 will wait because
Process 1 already has an IX lock. If no page or table lock will be needed,
Process 2 will place
X locks on the rows affected and also place it's own IX lock at the page and
table level.
Am I close?
TIA
Dave
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> To state it another way, an IX lock means that an exclusive lock may be
held
> at a lower level. For example, a row level exclusive lock will also
acquire
> a table level IX lock. The IX lock will prevent a conflicting table-level
> lock from being acquired without having to check individual locks that are
> lower in the hierarchy.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.co
mcast.com...
assumption?[vbcol=seagreen]
that[vbcol=seagreen]
>|||> Am I close?
You are correct in your description of Process 1 but it's probably better to
think about Process 2 in terms of lock escalation.
When Process 2 deletes a row, the IX locks are successfully acquired because
these are compatible with Process 1's existing IX locks. Process 2 gets the
exclusive lock on the row to be because it's on a different row than Process
1 is deleting.
When process 2 deletes a lot more rows, SQL Server will try to convert those
many row locks to a single table X lock. However, because that table-level
X lock isn't compatible with the existing Process 1 table IX lock, Process 2
waits until the lock is released.
You can read more about lock escalation in the SQL 2000 Books Online
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_con_7a_5ovi.htm).
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:Fo2dnWYuysxYL2zZnZ2dnUVZ_tmdnZ2d@.co
mcast.com...
> I'm sorry Dan, I'm having trouble understanding. Thanks for bearing with
> me.
> Is IX lock a safety mechanism of the lock manager?
> This is what I think occurs based on my reading and toying around with
> pubs...
> please correct me where I'm wrong.
> Process 1 deletes a row from the authors table. An X lock is placed on
> the
> row being
> deleted. IX is applied at the page and the table levels to signal to the
> lock manager that there's a lower
> level lock because something's going on at a page or row level.
> Now, Process 2 comes along to delete rows from the authors table. the
> lock
> manager says "Hold on, there
> are lower level row locks that must be checked before you may proceed".
> At
> that point, it checks
> to see if the rows it's deleting will cause a page or table lock. If so,
> Process 2 will wait because
> Process 1 already has an IX lock. If no page or table lock will be
> needed,
> Process 2 will place
> X locks on the rows affected and also place it's own IX lock at the page
> and
> table level.
> Am I close?
> TIA
> Dave
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> held
> acquire
> assumption?
> that
>|||Thank you, thank you! That's what I'm looking for!
I'm a visual learner.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eLLSBYfyGHA.1304@.TK2MSFTNGP05.phx.gbl...
> You are correct in your description of Process 1 but it's probably better
to
> think about Process 2 in terms of lock escalation.
> When Process 2 deletes a row, the IX locks are successfully acquired
because
> these are compatible with Process 1's existing IX locks. Process 2 gets
the
> exclusive lock on the row to be because it's on a different row than
Process
> 1 is deleting.
> When process 2 deletes a lot more rows, SQL Server will try to convert
those
> many row locks to a single table X lock. However, because that
table-level
> X lock isn't compatible with the existing Process 1 table IX lock, Process
2
> waits until the lock is released.
> You can read more about lock escalation in the SQL 2000 Books Online
>
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_con_7a_5ovi.htm).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:Fo2dnWYuysxYL2zZnZ2dnUVZ_tmdnZ2d@.co
mcast.com...
with[vbcol=seagreen]
the[vbcol=seagreen]
so,[vbcol=seagreen]
a[vbcol=seagreen]
manager[vbcol=seagreen]
get[vbcol=seagreen]
>
IX locks question
From what I'm getting, IX locks are just a safety mechanism to allow a
query
to lock at a higher grain (i.e. say, a table lock) if the lock manager needs
to
escalate row locks to page or table locks. Is this a correct assumption?
So, everytime I perform a DELETE or UPDATE can I expect that I will get
X locks on the rows affected and IX locks on the pages and on the table?
Is there a sample example against pubs or Northwind somewhere online that
illustrates this?To state it another way, an IX lock means that an exclusive lock may be held
at a lower level. For example, a row level exclusive lock will also acquire
a table level IX lock. The IX lock will prevent a conflicting table-level
lock from being acquired without having to check individual locks that are
lower in the hierarchy.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
> From what I'm getting, IX locks are just a safety mechanism to allow a
> query
> to lock at a higher grain (i.e. say, a table lock) if the lock manager
> needs
> to
> escalate row locks to page or table locks. Is this a correct assumption?
> So, everytime I perform a DELETE or UPDATE can I expect that I will get
> X locks on the rows affected and IX locks on the pages and on the table?
> Is there a sample example against pubs or Northwind somewhere online that
> illustrates this?
>|||I'm sorry Dan, I'm having trouble understanding. Thanks for bearing with
me.
Is IX lock a safety mechanism of the lock manager?
This is what I think occurs based on my reading and toying around with
pubs...
please correct me where I'm wrong.
Process 1 deletes a row from the authors table. An X lock is placed on the
row being
deleted. IX is applied at the page and the table levels to signal to the
lock manager that there's a lower
level lock because something's going on at a page or row level.
Now, Process 2 comes along to delete rows from the authors table. the lock
manager says "Hold on, there
are lower level row locks that must be checked before you may proceed". At
that point, it checks
to see if the rows it's deleting will cause a page or table lock. If so,
Process 2 will wait because
Process 1 already has an IX lock. If no page or table lock will be needed,
Process 2 will place
X locks on the rows affected and also place it's own IX lock at the page and
table level.
Am I close?
TIA
Dave
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> To state it another way, an IX lock means that an exclusive lock may be
held
> at a lower level. For example, a row level exclusive lock will also
acquire
> a table level IX lock. The IX lock will prevent a conflicting table-level
> lock from being acquired without having to check individual locks that are
> lower in the hierarchy.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
> >
> > From what I'm getting, IX locks are just a safety mechanism to allow a
> > query
> > to lock at a higher grain (i.e. say, a table lock) if the lock manager
> > needs
> > to
> > escalate row locks to page or table locks. Is this a correct
assumption?
> > So, everytime I perform a DELETE or UPDATE can I expect that I will get
> > X locks on the rows affected and IX locks on the pages and on the table?
> > Is there a sample example against pubs or Northwind somewhere online
that
> > illustrates this?
> >
> >
>|||> Am I close?
You are correct in your description of Process 1 but it's probably better to
think about Process 2 in terms of lock escalation.
When Process 2 deletes a row, the IX locks are successfully acquired because
these are compatible with Process 1's existing IX locks. Process 2 gets the
exclusive lock on the row to be because it's on a different row than Process
1 is deleting.
When process 2 deletes a lot more rows, SQL Server will try to convert those
many row locks to a single table X lock. However, because that table-level
X lock isn't compatible with the existing Process 1 table IX lock, Process 2
waits until the lock is released.
You can read more about lock escalation in the SQL 2000 Books Online
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\acdata.chm::/ac_8_con_7a_5ovi.htm).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:Fo2dnWYuysxYL2zZnZ2dnUVZ_tmdnZ2d@.comcast.com...
> I'm sorry Dan, I'm having trouble understanding. Thanks for bearing with
> me.
> Is IX lock a safety mechanism of the lock manager?
> This is what I think occurs based on my reading and toying around with
> pubs...
> please correct me where I'm wrong.
> Process 1 deletes a row from the authors table. An X lock is placed on
> the
> row being
> deleted. IX is applied at the page and the table levels to signal to the
> lock manager that there's a lower
> level lock because something's going on at a page or row level.
> Now, Process 2 comes along to delete rows from the authors table. the
> lock
> manager says "Hold on, there
> are lower level row locks that must be checked before you may proceed".
> At
> that point, it checks
> to see if the rows it's deleting will cause a page or table lock. If so,
> Process 2 will wait because
> Process 1 already has an IX lock. If no page or table lock will be
> needed,
> Process 2 will place
> X locks on the rows affected and also place it's own IX lock at the page
> and
> table level.
> Am I close?
> TIA
> Dave
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
>> To state it another way, an IX lock means that an exclusive lock may be
> held
>> at a lower level. For example, a row level exclusive lock will also
> acquire
>> a table level IX lock. The IX lock will prevent a conflicting
>> table-level
>> lock from being acquired without having to check individual locks that
>> are
>> lower in the hierarchy.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Dodo Lurker" <none@.noemailplease> wrote in message
>> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
>> >
>> > From what I'm getting, IX locks are just a safety mechanism to allow a
>> > query
>> > to lock at a higher grain (i.e. say, a table lock) if the lock manager
>> > needs
>> > to
>> > escalate row locks to page or table locks. Is this a correct
> assumption?
>> > So, everytime I perform a DELETE or UPDATE can I expect that I will get
>> > X locks on the rows affected and IX locks on the pages and on the
>> > table?
>> > Is there a sample example against pubs or Northwind somewhere online
> that
>> > illustrates this?
>> >
>> >
>>
>|||Thank you, thank you! That's what I'm looking for!
I'm a visual learner.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eLLSBYfyGHA.1304@.TK2MSFTNGP05.phx.gbl...
> > Am I close?
> You are correct in your description of Process 1 but it's probably better
to
> think about Process 2 in terms of lock escalation.
> When Process 2 deletes a row, the IX locks are successfully acquired
because
> these are compatible with Process 1's existing IX locks. Process 2 gets
the
> exclusive lock on the row to be because it's on a different row than
Process
> 1 is deleting.
> When process 2 deletes a lot more rows, SQL Server will try to convert
those
> many row locks to a single table X lock. However, because that
table-level
> X lock isn't compatible with the existing Process 1 table IX lock, Process
2
> waits until the lock is released.
> You can read more about lock escalation in the SQL 2000 Books Online
>
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_con_7a_5ovi.htm).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:Fo2dnWYuysxYL2zZnZ2dnUVZ_tmdnZ2d@.comcast.com...
> > I'm sorry Dan, I'm having trouble understanding. Thanks for bearing
with
> > me.
> >
> > Is IX lock a safety mechanism of the lock manager?
> >
> > This is what I think occurs based on my reading and toying around with
> > pubs...
> > please correct me where I'm wrong.
> >
> > Process 1 deletes a row from the authors table. An X lock is placed on
> > the
> > row being
> > deleted. IX is applied at the page and the table levels to signal to
the
> > lock manager that there's a lower
> > level lock because something's going on at a page or row level.
> >
> > Now, Process 2 comes along to delete rows from the authors table. the
> > lock
> > manager says "Hold on, there
> > are lower level row locks that must be checked before you may proceed".
> > At
> > that point, it checks
> > to see if the rows it's deleting will cause a page or table lock. If
so,
> > Process 2 will wait because
> > Process 1 already has an IX lock. If no page or table lock will be
> > needed,
> > Process 2 will place
> > X locks on the rows affected and also place it's own IX lock at the page
> > and
> > table level.
> >
> > Am I close?
> >
> > TIA
> > Dave
> >
> > "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> > news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> >> To state it another way, an IX lock means that an exclusive lock may be
> > held
> >> at a lower level. For example, a row level exclusive lock will also
> > acquire
> >> a table level IX lock. The IX lock will prevent a conflicting
> >> table-level
> >> lock from being acquired without having to check individual locks that
> >> are
> >> lower in the hierarchy.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Dodo Lurker" <none@.noemailplease> wrote in message
> >> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
> >> >
> >> > From what I'm getting, IX locks are just a safety mechanism to allow
a
> >> > query
> >> > to lock at a higher grain (i.e. say, a table lock) if the lock
manager
> >> > needs
> >> > to
> >> > escalate row locks to page or table locks. Is this a correct
> > assumption?
> >> > So, everytime I perform a DELETE or UPDATE can I expect that I will
get
> >> > X locks on the rows affected and IX locks on the pages and on the
> >> > table?
> >> > Is there a sample example against pubs or Northwind somewhere online
> > that
> >> > illustrates this?
> >> >
> >> >
> >>
> >>
> >
> >
>
query
to lock at a higher grain (i.e. say, a table lock) if the lock manager needs
to
escalate row locks to page or table locks. Is this a correct assumption?
So, everytime I perform a DELETE or UPDATE can I expect that I will get
X locks on the rows affected and IX locks on the pages and on the table?
Is there a sample example against pubs or Northwind somewhere online that
illustrates this?To state it another way, an IX lock means that an exclusive lock may be held
at a lower level. For example, a row level exclusive lock will also acquire
a table level IX lock. The IX lock will prevent a conflicting table-level
lock from being acquired without having to check individual locks that are
lower in the hierarchy.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
> From what I'm getting, IX locks are just a safety mechanism to allow a
> query
> to lock at a higher grain (i.e. say, a table lock) if the lock manager
> needs
> to
> escalate row locks to page or table locks. Is this a correct assumption?
> So, everytime I perform a DELETE or UPDATE can I expect that I will get
> X locks on the rows affected and IX locks on the pages and on the table?
> Is there a sample example against pubs or Northwind somewhere online that
> illustrates this?
>|||I'm sorry Dan, I'm having trouble understanding. Thanks for bearing with
me.
Is IX lock a safety mechanism of the lock manager?
This is what I think occurs based on my reading and toying around with
pubs...
please correct me where I'm wrong.
Process 1 deletes a row from the authors table. An X lock is placed on the
row being
deleted. IX is applied at the page and the table levels to signal to the
lock manager that there's a lower
level lock because something's going on at a page or row level.
Now, Process 2 comes along to delete rows from the authors table. the lock
manager says "Hold on, there
are lower level row locks that must be checked before you may proceed". At
that point, it checks
to see if the rows it's deleting will cause a page or table lock. If so,
Process 2 will wait because
Process 1 already has an IX lock. If no page or table lock will be needed,
Process 2 will place
X locks on the rows affected and also place it's own IX lock at the page and
table level.
Am I close?
TIA
Dave
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> To state it another way, an IX lock means that an exclusive lock may be
held
> at a lower level. For example, a row level exclusive lock will also
acquire
> a table level IX lock. The IX lock will prevent a conflicting table-level
> lock from being acquired without having to check individual locks that are
> lower in the hierarchy.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
> >
> > From what I'm getting, IX locks are just a safety mechanism to allow a
> > query
> > to lock at a higher grain (i.e. say, a table lock) if the lock manager
> > needs
> > to
> > escalate row locks to page or table locks. Is this a correct
assumption?
> > So, everytime I perform a DELETE or UPDATE can I expect that I will get
> > X locks on the rows affected and IX locks on the pages and on the table?
> > Is there a sample example against pubs or Northwind somewhere online
that
> > illustrates this?
> >
> >
>|||> Am I close?
You are correct in your description of Process 1 but it's probably better to
think about Process 2 in terms of lock escalation.
When Process 2 deletes a row, the IX locks are successfully acquired because
these are compatible with Process 1's existing IX locks. Process 2 gets the
exclusive lock on the row to be because it's on a different row than Process
1 is deleting.
When process 2 deletes a lot more rows, SQL Server will try to convert those
many row locks to a single table X lock. However, because that table-level
X lock isn't compatible with the existing Process 1 table IX lock, Process 2
waits until the lock is released.
You can read more about lock escalation in the SQL 2000 Books Online
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\acdata.chm::/ac_8_con_7a_5ovi.htm).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:Fo2dnWYuysxYL2zZnZ2dnUVZ_tmdnZ2d@.comcast.com...
> I'm sorry Dan, I'm having trouble understanding. Thanks for bearing with
> me.
> Is IX lock a safety mechanism of the lock manager?
> This is what I think occurs based on my reading and toying around with
> pubs...
> please correct me where I'm wrong.
> Process 1 deletes a row from the authors table. An X lock is placed on
> the
> row being
> deleted. IX is applied at the page and the table levels to signal to the
> lock manager that there's a lower
> level lock because something's going on at a page or row level.
> Now, Process 2 comes along to delete rows from the authors table. the
> lock
> manager says "Hold on, there
> are lower level row locks that must be checked before you may proceed".
> At
> that point, it checks
> to see if the rows it's deleting will cause a page or table lock. If so,
> Process 2 will wait because
> Process 1 already has an IX lock. If no page or table lock will be
> needed,
> Process 2 will place
> X locks on the rows affected and also place it's own IX lock at the page
> and
> table level.
> Am I close?
> TIA
> Dave
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
>> To state it another way, an IX lock means that an exclusive lock may be
> held
>> at a lower level. For example, a row level exclusive lock will also
> acquire
>> a table level IX lock. The IX lock will prevent a conflicting
>> table-level
>> lock from being acquired without having to check individual locks that
>> are
>> lower in the hierarchy.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Dodo Lurker" <none@.noemailplease> wrote in message
>> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
>> >
>> > From what I'm getting, IX locks are just a safety mechanism to allow a
>> > query
>> > to lock at a higher grain (i.e. say, a table lock) if the lock manager
>> > needs
>> > to
>> > escalate row locks to page or table locks. Is this a correct
> assumption?
>> > So, everytime I perform a DELETE or UPDATE can I expect that I will get
>> > X locks on the rows affected and IX locks on the pages and on the
>> > table?
>> > Is there a sample example against pubs or Northwind somewhere online
> that
>> > illustrates this?
>> >
>> >
>>
>|||Thank you, thank you! That's what I'm looking for!
I'm a visual learner.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eLLSBYfyGHA.1304@.TK2MSFTNGP05.phx.gbl...
> > Am I close?
> You are correct in your description of Process 1 but it's probably better
to
> think about Process 2 in terms of lock escalation.
> When Process 2 deletes a row, the IX locks are successfully acquired
because
> these are compatible with Process 1's existing IX locks. Process 2 gets
the
> exclusive lock on the row to be because it's on a different row than
Process
> 1 is deleting.
> When process 2 deletes a lot more rows, SQL Server will try to convert
those
> many row locks to a single table X lock. However, because that
table-level
> X lock isn't compatible with the existing Process 1 table IX lock, Process
2
> waits until the lock is released.
> You can read more about lock escalation in the SQL 2000 Books Online
>
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_con_7a_5ovi.htm).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:Fo2dnWYuysxYL2zZnZ2dnUVZ_tmdnZ2d@.comcast.com...
> > I'm sorry Dan, I'm having trouble understanding. Thanks for bearing
with
> > me.
> >
> > Is IX lock a safety mechanism of the lock manager?
> >
> > This is what I think occurs based on my reading and toying around with
> > pubs...
> > please correct me where I'm wrong.
> >
> > Process 1 deletes a row from the authors table. An X lock is placed on
> > the
> > row being
> > deleted. IX is applied at the page and the table levels to signal to
the
> > lock manager that there's a lower
> > level lock because something's going on at a page or row level.
> >
> > Now, Process 2 comes along to delete rows from the authors table. the
> > lock
> > manager says "Hold on, there
> > are lower level row locks that must be checked before you may proceed".
> > At
> > that point, it checks
> > to see if the rows it's deleting will cause a page or table lock. If
so,
> > Process 2 will wait because
> > Process 1 already has an IX lock. If no page or table lock will be
> > needed,
> > Process 2 will place
> > X locks on the rows affected and also place it's own IX lock at the page
> > and
> > table level.
> >
> > Am I close?
> >
> > TIA
> > Dave
> >
> > "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> > news:e8HruFZyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> >> To state it another way, an IX lock means that an exclusive lock may be
> > held
> >> at a lower level. For example, a row level exclusive lock will also
> > acquire
> >> a table level IX lock. The IX lock will prevent a conflicting
> >> table-level
> >> lock from being acquired without having to check individual locks that
> >> are
> >> lower in the hierarchy.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Dodo Lurker" <none@.noemailplease> wrote in message
> >> news:xuedneafruC0h2zZnZ2dnUVZ_rOdnZ2d@.comcast.com...
> >> >
> >> > From what I'm getting, IX locks are just a safety mechanism to allow
a
> >> > query
> >> > to lock at a higher grain (i.e. say, a table lock) if the lock
manager
> >> > needs
> >> > to
> >> > escalate row locks to page or table locks. Is this a correct
> > assumption?
> >> > So, everytime I perform a DELETE or UPDATE can I expect that I will
get
> >> > X locks on the rows affected and IX locks on the pages and on the
> >> > table?
> >> > Is there a sample example against pubs or Northwind somewhere online
> > that
> >> > illustrates this?
> >> >
> >> >
> >>
> >>
> >
> >
>
IX lock on a table
If I have an IX lock on a table, what kind of DMLs am I restricted doing ?
Hi Hassan
IX lock on the table means that someone has X lock on a row or a page. So
you can do any modifications on other rows or pages. You just can't do
anything that requires X lock on the whole table. X lock is incompatible
with IX, but IX can be combined with another IX (two processes with X lock
on separate rows each have IX on the table).
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
> If I have an IX lock on a table, what kind of DMLs am I restricted doing ?
>
|||So how can I find out whats holding an X lock to this table ... I mean which
spid ? if you've read my previous thread, Im having some orphaned
connections and these spids that Im trying to kill have IX locks on the
table and wouldnt kill..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> Hi Hassan
> IX lock on the table means that someone has X lock on a row or a page. So
> you can do any modifications on other rows or pages. You just can't do
> anything that requires X lock on the whole table. X lock is incompatible
> with IX, but IX can be combined with another IX (two processes with X
lock[vbcol=seagreen]
> on separate rows each have IX on the table).
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
?
>
|||sp_lock shows you who's holding what locks.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> So how can I find out whats holding an X lock to this table ... I mean
> which
> spid ? if you've read my previous thread, Im having some orphaned
> connections and these spids that Im trying to kill have IX locks on the
> table and wouldnt kill..
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> lock
> ?
>
|||Yup cant really find anything due to it being orphaned, but found something
else for other spids when i ran sp_lock
Came across a type of "APP" and resource as "DNSQ4b945027" as an example
for some spids.. Any idea what types of locks these are
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> sp_lock shows you who's holding what locks.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
So[vbcol=seagreen]
incompatible
>
|||Those are Application locks, defined by the user (or application). You can
read about Application Lock in the Books Online.
So how do you know someone is holding an X lock on a table, if you can't see
it in sp_lock. Or are you just assuming that, because I said that is what
blocks an IX lock?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Yup cant really find anything due to it being orphaned, but found
> something
> else for other spids when i ran sp_lock
> Came across a type of "APP" and resource as "DNSQ4b945027" as an example
> for some spids.. Any idea what types of locks these are
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
> So
> incompatible
>
|||Yes I have an IX lock on a table and you mentioned that I would see if if
one has an X lock on a page or a row. But could not find anything
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
> Those are Application locks, defined by the user (or application). You can
> read about Application Lock in the Books Online.
> So how do you know someone is holding an X lock on a table, if you can't
see[vbcol=seagreen]
> it in sp_lock. Or are you just assuming that, because I said that is what
> blocks an IX lock?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
example[vbcol=seagreen]
mean[vbcol=seagreen]
the[vbcol=seagreen]
page.[vbcol=seagreen]
do[vbcol=seagreen]
X
>
|||How are you seeing the IX lock? Run sp_lock, and look at all the rows for
the same spid that has the IX lock. In fact, you can pass the spid as an
argument to sp_lock.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl...
> Yes I have an IX lock on a table and you mentioned that I would see if if
> one has an X lock on a page or a row. But could not find anything
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
> see
> example
> mean
> the
> page.
> do
> X
>
|||sp_lock <orphaned spid>
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
94 8 0 0 DB S GRANT
94 8 142321797 0 TAB IX GRANT
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> How are you seeing the IX lock? Run sp_lock, and look at all the rows for
> the same spid that has the IX lock. In fact, you can pass the spid as an
> argument to sp_lock.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl...
if[vbcol=seagreen]
can't[vbcol=seagreen]
what[vbcol=seagreen]
on[vbcol=seagreen]
can't
>
|||You can't have just an IX lock on a table (can you?). An intent lock is
always as a result of a more granular lock. So if you have an TAB IX
lock then you must also have, for spid 94, a PAG X lock, a KEY X lock or
a RID X lock. Are there other locks on that spid that you're not
showing us or is that it?
To answer your original question, if there is an existing IX lock on a
table, any lock request other than an intent lock (IX or IS) on the
table will be blocked. So you can get a S, U or X lock on a page or row
in the table (assuming it's not the page or row that spid 94 is X
locking), which will give you an IS or IX lock on the table. So that
means you can do a SELECT, INSERT, UPDATE or DELETE on the table as long
as you only need to lock rows or pages in the table and not the whole table.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:
>sp_lock <orphaned spid>
>spid dbid ObjId IndId Type Resource Mode Status
>-- -- -- -- -- -- -- --
>94 8 0 0 DB S GRANT
>94 8 142321797 0 TAB IX GRANT
>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl...
>
>if
>
>can't
>
>what
>
>on
>
>can't
>
>
>
Hi Hassan
IX lock on the table means that someone has X lock on a row or a page. So
you can do any modifications on other rows or pages. You just can't do
anything that requires X lock on the whole table. X lock is incompatible
with IX, but IX can be combined with another IX (two processes with X lock
on separate rows each have IX on the table).
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
> If I have an IX lock on a table, what kind of DMLs am I restricted doing ?
>
|||So how can I find out whats holding an X lock to this table ... I mean which
spid ? if you've read my previous thread, Im having some orphaned
connections and these spids that Im trying to kill have IX locks on the
table and wouldnt kill..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> Hi Hassan
> IX lock on the table means that someone has X lock on a row or a page. So
> you can do any modifications on other rows or pages. You just can't do
> anything that requires X lock on the whole table. X lock is incompatible
> with IX, but IX can be combined with another IX (two processes with X
lock[vbcol=seagreen]
> on separate rows each have IX on the table).
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
?
>
|||sp_lock shows you who's holding what locks.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> So how can I find out whats holding an X lock to this table ... I mean
> which
> spid ? if you've read my previous thread, Im having some orphaned
> connections and these spids that Im trying to kill have IX locks on the
> table and wouldnt kill..
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> lock
> ?
>
|||Yup cant really find anything due to it being orphaned, but found something
else for other spids when i ran sp_lock
Came across a type of "APP" and resource as "DNSQ4b945027" as an example
for some spids.. Any idea what types of locks these are
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> sp_lock shows you who's holding what locks.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
So[vbcol=seagreen]
incompatible
>
|||Those are Application locks, defined by the user (or application). You can
read about Application Lock in the Books Online.
So how do you know someone is holding an X lock on a table, if you can't see
it in sp_lock. Or are you just assuming that, because I said that is what
blocks an IX lock?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Yup cant really find anything due to it being orphaned, but found
> something
> else for other spids when i ran sp_lock
> Came across a type of "APP" and resource as "DNSQ4b945027" as an example
> for some spids.. Any idea what types of locks these are
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
> So
> incompatible
>
|||Yes I have an IX lock on a table and you mentioned that I would see if if
one has an X lock on a page or a row. But could not find anything
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
> Those are Application locks, defined by the user (or application). You can
> read about Application Lock in the Books Online.
> So how do you know someone is holding an X lock on a table, if you can't
see[vbcol=seagreen]
> it in sp_lock. Or are you just assuming that, because I said that is what
> blocks an IX lock?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
example[vbcol=seagreen]
mean[vbcol=seagreen]
the[vbcol=seagreen]
page.[vbcol=seagreen]
do[vbcol=seagreen]
X
>
|||How are you seeing the IX lock? Run sp_lock, and look at all the rows for
the same spid that has the IX lock. In fact, you can pass the spid as an
argument to sp_lock.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl...
> Yes I have an IX lock on a table and you mentioned that I would see if if
> one has an X lock on a page or a row. But could not find anything
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
> see
> example
> mean
> the
> page.
> do
> X
>
|||sp_lock <orphaned spid>
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
94 8 0 0 DB S GRANT
94 8 142321797 0 TAB IX GRANT
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> How are you seeing the IX lock? Run sp_lock, and look at all the rows for
> the same spid that has the IX lock. In fact, you can pass the spid as an
> argument to sp_lock.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl...
if[vbcol=seagreen]
can't[vbcol=seagreen]
what[vbcol=seagreen]
on[vbcol=seagreen]
can't
>
|||You can't have just an IX lock on a table (can you?). An intent lock is
always as a result of a more granular lock. So if you have an TAB IX
lock then you must also have, for spid 94, a PAG X lock, a KEY X lock or
a RID X lock. Are there other locks on that spid that you're not
showing us or is that it?
To answer your original question, if there is an existing IX lock on a
table, any lock request other than an intent lock (IX or IS) on the
table will be blocked. So you can get a S, U or X lock on a page or row
in the table (assuming it's not the page or row that spid 94 is X
locking), which will give you an IS or IX lock on the table. So that
means you can do a SELECT, INSERT, UPDATE or DELETE on the table as long
as you only need to lock rows or pages in the table and not the whole table.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:
>sp_lock <orphaned spid>
>spid dbid ObjId IndId Type Resource Mode Status
>-- -- -- -- -- -- -- --
>94 8 0 0 DB S GRANT
>94 8 142321797 0 TAB IX GRANT
>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl...
>
>if
>
>can't
>
>what
>
>on
>
>can't
>
>
>
IX lock on a table
If I have an IX lock on a table, what kind of DMLs am I restricted doing ?Hi Hassan
IX lock on the table means that someone has X lock on a row or a page. So
you can do any modifications on other rows or pages. You just can't do
anything that requires X lock on the whole table. X lock is incompatible
with IX, but IX can be combined with another IX (two processes with X lock
on separate rows each have IX on the table).
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
> If I have an IX lock on a table, what kind of DMLs am I restricted doing ?
>|||So how can I find out whats holding an X lock to this table ... I mean which
spid ? if you've read my previous thread, Im having some orphaned
connections and these spids that Im trying to kill have IX locks on the
table and wouldnt kill..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> Hi Hassan
> IX lock on the table means that someone has X lock on a row or a page. So
> you can do any modifications on other rows or pages. You just can't do
> anything that requires X lock on the whole table. X lock is incompatible
> with IX, but IX can be combined with another IX (two processes with X
lock
> on separate rows each have IX on the table).
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
> > If I have an IX lock on a table, what kind of DMLs am I restricted doing
?
> >
> >
>|||sp_lock shows you who's holding what locks.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> So how can I find out whats holding an X lock to this table ... I mean
> which
> spid ? if you've read my previous thread, Im having some orphaned
> connections and these spids that Im trying to kill have IX locks on the
> table and wouldnt kill..
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
>> Hi Hassan
>> IX lock on the table means that someone has X lock on a row or a page. So
>> you can do any modifications on other rows or pages. You just can't do
>> anything that requires X lock on the whole table. X lock is incompatible
>> with IX, but IX can be combined with another IX (two processes with X
> lock
>> on separate rows each have IX on the table).
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
>> > If I have an IX lock on a table, what kind of DMLs am I restricted
>> > doing
> ?
>> >
>> >
>>
>|||Yup cant really find anything due to it being orphaned, but found something
else for other spids when i ran sp_lock
Came across a type of "APP" and resource as "DNSQ4b945027" as an example
for some spids.. Any idea what types of locks these are
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
> sp_lock shows you who's holding what locks.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> > So how can I find out whats holding an X lock to this table ... I mean
> > which
> > spid ? if you've read my previous thread, Im having some orphaned
> > connections and these spids that Im trying to kill have IX locks on the
> > table and wouldnt kill..
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> >> Hi Hassan
> >>
> >> IX lock on the table means that someone has X lock on a row or a page.
So
> >> you can do any modifications on other rows or pages. You just can't do
> >> anything that requires X lock on the whole table. X lock is
incompatible
> >> with IX, but IX can be combined with another IX (two processes with X
> > lock
> >> on separate rows each have IX on the table).
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> >> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
> >> > If I have an IX lock on a table, what kind of DMLs am I restricted
> >> > doing
> > ?
> >> >
> >> >
> >>
> >>
> >
> >
>|||Those are Application locks, defined by the user (or application). You can
read about Application Lock in the Books Online.
So how do you know someone is holding an X lock on a table, if you can't see
it in sp_lock. Or are you just assuming that, because I said that is what
blocks an IX lock?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Yup cant really find anything due to it being orphaned, but found
> something
> else for other spids when i ran sp_lock
> Came across a type of "APP" and resource as "DNSQ4b945027" as an example
> for some spids.. Any idea what types of locks these are
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
>> sp_lock shows you who's holding what locks.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
>> > So how can I find out whats holding an X lock to this table ... I mean
>> > which
>> > spid ? if you've read my previous thread, Im having some orphaned
>> > connections and these spids that Im trying to kill have IX locks on the
>> > table and wouldnt kill..
>> >
>> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> > news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
>> >> Hi Hassan
>> >>
>> >> IX lock on the table means that someone has X lock on a row or a page.
> So
>> >> you can do any modifications on other rows or pages. You just can't do
>> >> anything that requires X lock on the whole table. X lock is
> incompatible
>> >> with IX, but IX can be combined with another IX (two processes with X
>> > lock
>> >> on separate rows each have IX on the table).
>> >>
>> >> --
>> >> HTH
>> >> --
>> >> Kalen Delaney
>> >> SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> >> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
>> >> > If I have an IX lock on a table, what kind of DMLs am I restricted
>> >> > doing
>> > ?
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Yes I have an IX lock on a table and you mentioned that I would see if if
one has an X lock on a page or a row. But could not find anything
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
> Those are Application locks, defined by the user (or application). You can
> read about Application Lock in the Books Online.
> So how do you know someone is holding an X lock on a table, if you can't
see
> it in sp_lock. Or are you just assuming that, because I said that is what
> blocks an IX lock?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> > Yup cant really find anything due to it being orphaned, but found
> > something
> > else for other spids when i ran sp_lock
> >
> > Came across a type of "APP" and resource as "DNSQ4b945027" as an
example
> > for some spids.. Any idea what types of locks these are
> >
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
> >> sp_lock shows you who's holding what locks.
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> >> news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> >> > So how can I find out whats holding an X lock to this table ... I
mean
> >> > which
> >> > spid ? if you've read my previous thread, Im having some orphaned
> >> > connections and these spids that Im trying to kill have IX locks on
the
> >> > table and wouldnt kill..
> >> >
> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> >> > news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> >> >> Hi Hassan
> >> >>
> >> >> IX lock on the table means that someone has X lock on a row or a
page.
> > So
> >> >> you can do any modifications on other rows or pages. You just can't
do
> >> >> anything that requires X lock on the whole table. X lock is
> > incompatible
> >> >> with IX, but IX can be combined with another IX (two processes with
X
> >> > lock
> >> >> on separate rows each have IX on the table).
> >> >>
> >> >> --
> >> >> HTH
> >> >> --
> >> >> Kalen Delaney
> >> >> SQL Server MVP
> >> >> www.SolidQualityLearning.com
> >> >>
> >> >>
> >> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> >> >> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
> >> >> > If I have an IX lock on a table, what kind of DMLs am I restricted
> >> >> > doing
> >> > ?
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||How are you seeing the IX lock? Run sp_lock, and look at all the rows for
the same spid that has the IX lock. In fact, you can pass the spid as an
argument to sp_lock.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl...
> Yes I have an IX lock on a table and you mentioned that I would see if if
> one has an X lock on a page or a row. But could not find anything
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
>> Those are Application locks, defined by the user (or application). You
>> can
>> read about Application Lock in the Books Online.
>> So how do you know someone is holding an X lock on a table, if you can't
> see
>> it in sp_lock. Or are you just assuming that, because I said that is what
>> blocks an IX lock?
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
>> > Yup cant really find anything due to it being orphaned, but found
>> > something
>> > else for other spids when i ran sp_lock
>> >
>> > Came across a type of "APP" and resource as "DNSQ4b945027" as an
> example
>> > for some spids.. Any idea what types of locks these are
>> >
>> >
>> >
>> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> > news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
>> >> sp_lock shows you who's holding what locks.
>> >>
>> >> --
>> >> HTH
>> >> --
>> >> Kalen Delaney
>> >> SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> >> news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
>> >> > So how can I find out whats holding an X lock to this table ... I
> mean
>> >> > which
>> >> > spid ? if you've read my previous thread, Im having some orphaned
>> >> > connections and these spids that Im trying to kill have IX locks on
> the
>> >> > table and wouldnt kill..
>> >> >
>> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> >> > news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
>> >> >> Hi Hassan
>> >> >>
>> >> >> IX lock on the table means that someone has X lock on a row or a
> page.
>> > So
>> >> >> you can do any modifications on other rows or pages. You just can't
> do
>> >> >> anything that requires X lock on the whole table. X lock is
>> > incompatible
>> >> >> with IX, but IX can be combined with another IX (two processes
>> >> >> with
> X
>> >> > lock
>> >> >> on separate rows each have IX on the table).
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >> --
>> >> >> Kalen Delaney
>> >> >> SQL Server MVP
>> >> >> www.SolidQualityLearning.com
>> >> >>
>> >> >>
>> >> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> >> >> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
>> >> >> > If I have an IX lock on a table, what kind of DMLs am I
>> >> >> > restricted
>> >> >> > doing
>> >> > ?
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||sp_lock <orphaned spid>
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
94 8 0 0 DB S GRANT
94 8 142321797 0 TAB IX GRANT
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl...
> How are you seeing the IX lock? Run sp_lock, and look at all the rows for
> the same spid that has the IX lock. In fact, you can pass the spid as an
> argument to sp_lock.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl...
> > Yes I have an IX lock on a table and you mentioned that I would see if
if
> > one has an X lock on a page or a row. But could not find anything
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
> >> Those are Application locks, defined by the user (or application). You
> >> can
> >> read about Application Lock in the Books Online.
> >>
> >> So how do you know someone is holding an X lock on a table, if you
can't
> > see
> >> it in sp_lock. Or are you just assuming that, because I said that is
what
> >> blocks an IX lock?
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> >> news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> >> > Yup cant really find anything due to it being orphaned, but found
> >> > something
> >> > else for other spids when i ran sp_lock
> >> >
> >> > Came across a type of "APP" and resource as "DNSQ4b945027" as an
> > example
> >> > for some spids.. Any idea what types of locks these are
> >> >
> >> >
> >> >
> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> >> > news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
> >> >> sp_lock shows you who's holding what locks.
> >> >>
> >> >> --
> >> >> HTH
> >> >> --
> >> >> Kalen Delaney
> >> >> SQL Server MVP
> >> >> www.SolidQualityLearning.com
> >> >>
> >> >>
> >> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> >> >> news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> >> >> > So how can I find out whats holding an X lock to this table ... I
> > mean
> >> >> > which
> >> >> > spid ? if you've read my previous thread, Im having some orphaned
> >> >> > connections and these spids that Im trying to kill have IX locks
on
> > the
> >> >> > table and wouldnt kill..
> >> >> >
> >> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> >> >> > news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> >> >> >> Hi Hassan
> >> >> >>
> >> >> >> IX lock on the table means that someone has X lock on a row or a
> > page.
> >> > So
> >> >> >> you can do any modifications on other rows or pages. You just
can't
> > do
> >> >> >> anything that requires X lock on the whole table. X lock is
> >> > incompatible
> >> >> >> with IX, but IX can be combined with another IX (two processes
> >> >> >> with
> > X
> >> >> > lock
> >> >> >> on separate rows each have IX on the table).
> >> >> >>
> >> >> >> --
> >> >> >> HTH
> >> >> >> --
> >> >> >> Kalen Delaney
> >> >> >> SQL Server MVP
> >> >> >> www.SolidQualityLearning.com
> >> >> >>
> >> >> >>
> >> >> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> >> >> >> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
> >> >> >> > If I have an IX lock on a table, what kind of DMLs am I
> >> >> >> > restricted
> >> >> >> > doing
> >> >> > ?
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||This is a multi-part message in MIME format.
--060208030708050403020006
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
You can't have just an IX lock on a table (can you?). An intent lock is
always as a result of a more granular lock. So if you have an TAB IX
lock then you must also have, for spid 94, a PAG X lock, a KEY X lock or
a RID X lock. Are there other locks on that spid that you're not
showing us or is that it?
To answer your original question, if there is an existing IX lock on a
table, any lock request other than an intent lock (IX or IS) on the
table will be blocked. So you can get a S, U or X lock on a page or row
in the table (assuming it's not the page or row that spid 94 is X
locking), which will give you an IS or IX lock on the table. So that
means you can do a SELECT, INSERT, UPDATE or DELETE on the table as long
as you only need to lock rows or pages in the table and not the whole table.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:
>sp_lock <orphaned spid>
>spid dbid ObjId IndId Type Resource Mode Status
>-- -- -- -- -- -- -- --
>94 8 0 0 DB S GRANT
>94 8 142321797 0 TAB IX GRANT
>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl...
>
>>How are you seeing the IX lock? Run sp_lock, and look at all the rows for
>>the same spid that has the IX lock. In fact, you can pass the spid as an
>>argument to sp_lock.
>>--
>>HTH
>>--
>>Kalen Delaney
>>SQL Server MVP
>>www.SolidQualityLearning.com
>>
>>"Hassan" <fatima_ja@.hotmail.com> wrote in message
>>news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl...
>>
>>Yes I have an IX lock on a table and you mentioned that I would see if
>>
>if
>
>>one has an X lock on a page or a row. But could not find anything
>>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>>news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
>>
>>Those are Application locks, defined by the user (or application). You
>>can
>>read about Application Lock in the Books Online.
>>So how do you know someone is holding an X lock on a table, if you
>>
>can't
>
>>see
>>
>>it in sp_lock. Or are you just assuming that, because I said that is
>>
>what
>
>>blocks an IX lock?
>>--
>>HTH
>>--
>>Kalen Delaney
>>SQL Server MVP
>>www.SolidQualityLearning.com
>>
>>"Hassan" <fatima_ja@.hotmail.com> wrote in message
>>news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
>>
>>Yup cant really find anything due to it being orphaned, but found
>>something
>>else for other spids when i ran sp_lock
>>Came across a type of "APP" and resource as "DNSQ4b945027" as an
>>
>>example
>>
>>for some spids.. Any idea what types of locks these are
>>
>>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>>news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
>>
>>sp_lock shows you who's holding what locks.
>>--
>>HTH
>>--
>>Kalen Delaney
>>SQL Server MVP
>>www.SolidQualityLearning.com
>>
>>"Hassan" <fatima_ja@.hotmail.com> wrote in message
>>news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
>>
>>>So how can I find out whats holding an X lock to this table ... I
>>>
>>>
>>mean
>>
>>>which
>>>spid ? if you've read my previous thread, Im having some orphaned
>>>connections and these spids that Im trying to kill have IX locks
>>>
>>>
>on
>
>>the
>>
>>>table and wouldnt kill..
>>>
>>>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>>>news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
>>>
>>>
>>>Hi Hassan
>>>
>>>IX lock on the table means that someone has X lock on a row or a
>>>
>>>
>>page.
>>
>>So
>>
>>>you can do any modifications on other rows or pages. You just
>>>
>>>
>can't
>
>>do
>>
>>>anything that requires X lock on the whole table. X lock is
>>>
>>>
>>incompatible
>>
>>>with IX, but IX can be combined with another IX (two processes
>>>with
>>>
>>>
>>X
>>
>>>lock
>>>
>>>
>>>on separate rows each have IX on the table).
>>>
>>>--
>>>HTH
>>>--
>>>Kalen Delaney
>>>SQL Server MVP
>>>www.SolidQualityLearning.com
>>>
>>>
>>>"Hassan" <fatima_ja@.hotmail.com> wrote in message
>>>news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
>>>
>>>
>>>If I have an IX lock on a table, what kind of DMLs am I
>>>restricted
>>>doing
>>>
>>>
>>>?
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
>
--060208030708050403020006
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You can't have just an IX lock on a table (can you?). An intent
lock is always as a result of a more granular lock. So if you have an
TAB IX lock then you must also have, for spid 94, a PAG X lock, a KEY X
lock or a RID X lock. Are there other locks on that spid that you're
not showing us or is that it?<br>
<br>
To answer your original question, if there is an existing IX lock on a
table, any lock request other than an intent lock (IX or IS) on the
table will be blocked. So you can get a S, U or X lock on a page or
row in the table (assuming it's not the page or row that spid 94 is X
locking), which will give you an IS or IX lock on the table. So that
means you can do a SELECT, INSERT, UPDATE or DELETE on the table as
long as you only need to lock rows or pages in the table and not the
whole table.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Hassan wrote:
<blockquote cite="midO4wukBLFFHA.2832@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">sp_lock <orphaned spid>
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
94 8 0 0 DB S GRANT
94 8 142321797 0 TAB IX GRANT
"Kalen Delaney" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl">news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">How are you seeing the IX lock? Run sp_lock, and look at all the rows for
the same spid that has the IX lock. In fact, you can pass the spid as an
argument to sp_lock.
--
HTH
--
Kalen Delaney
SQL Server MVP
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.SolidQualityLearning.com">www.SolidQualityLearning.com</a>
"Hassan" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:fatima_ja@.hotmail.com"><fatima_ja@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl">news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Yes I have an IX lock on a table and you mentioned that I would see if
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->if
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">one has an X lock on a page or a row. But could not find anything
"Kalen Delaney" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl">news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Those are Application locks, defined by the user (or application). You
can
read about Application Lock in the Books Online.
So how do you know someone is holding an X lock on a table, if you
</pre>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!-->can't
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">see
</pre>
<blockquote type="cite">
<pre wrap="">it in sp_lock. Or are you just assuming that, because I said that is
</pre>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!-->what
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">blocks an IX lock?
--
HTH
--
Kalen Delaney
SQL Server MVP
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.SolidQualityLearning.com">www.SolidQualityLearning.com</a>
"Hassan" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:fatima_ja@.hotmail.com"><fatima_ja@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl">news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Yup cant really find anything due to it being orphaned, but found
something
else for other spids when i ran sp_lock
Came across a type of "APP" and resource as "DNSQ4b945027" as an
</pre>
</blockquote>
</blockquote>
<pre wrap="">example
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">for some spids.. Any idea what types of locks these are
"Kalen Delaney" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl">news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">sp_lock shows you who's holding what locks.
--
HTH
--
Kalen Delaney
SQL Server MVP
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.SolidQualityLearning.com">www.SolidQualityLearning.com</a>
"Hassan" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:fatima_ja@.hotmail.com"><fatima_ja@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl">news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">So how can I find out whats holding an X lock to this table ... I
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="">mean
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">which
spid ? if you've read my previous thread, Im having some orphaned
connections and these spids that Im trying to kill have IX locks
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!-->on
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">the
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">table and wouldnt kill..
"Kalen Delaney" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl">news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi Hassan
IX lock on the table means that someone has X lock on a row or a
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="">page.
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">So
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">you can do any modifications on other rows or pages. You just
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!-->can't
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">do
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">anything that requires X lock on the whole table. X lock is
</pre>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="">incompatible
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">with IX, but IX can be combined with another IX (two processes
with
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="">X
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">lock
</pre>
<blockquote type="cite">
<pre wrap="">on separate rows each have IX on the table).
--
HTH
--
Kalen Delaney
SQL Server MVP
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.SolidQualityLearning.com">www.SolidQualityLearning.com</a>
"Hassan" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:fatima_ja@.hotmail.com"><fatima_ja@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl">news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">If I have an IX lock on a table, what kind of DMLs am I
restricted
doing
</pre>
</blockquote>
</blockquote>
<pre wrap="">?
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--060208030708050403020006--|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C514E6.A3E1B940
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Not hiding anything.. And yes its weird and I know a restart of the SQL =Services would fix it but I'd like to know more about the internals on =the PSS Structure that holds this info and that a kill command cannot =get to it
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:uxaP6LLFFHA.624@.TK2MSFTNGP15.phx.gbl...
You can't have just an IX lock on a table (can you?). An intent lock =is always as a result of a more granular lock. So if you have an TAB IX =lock then you must also have, for spid 94, a PAG X lock, a KEY X lock or =a RID X lock. Are there other locks on that spid that you're not =showing us or is that it?
To answer your original question, if there is an existing IX lock on a =table, any lock request other than an intent lock (IX or IS) on the =table will be blocked. So you can get a S, U or X lock on a page or row =in the table (assuming it's not the page or row that spid 94 is X =locking), which will give you an IS or IX lock on the table. So that =means you can do a SELECT, INSERT, UPDATE or DELETE on the table as long =as you only need to lock rows or pages in the table and not the whole =table.
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W =http://www.mallesons.com=20
Hassan wrote: sp_lock <orphaned spid>
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- -- 94 8 0 0 DB S GRANT
94 8 142321797 0 TAB IX GRANT
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl...
How are you seeing the IX lock? Run sp_lock, and look at all the rows =for
the same spid that has the IX lock. In fact, you can pass the spid as an
argument to sp_lock.
-- HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl...
Yes I have an IX lock on a table and you mentioned that I would see =if
if
one has an X lock on a page or a row. But could not find anything
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
Those are Application locks, defined by the user (or application). =You
can
read about Application Lock in the Books Online.
So how do you know someone is holding an X lock on a table, if you
can't
see
it in sp_lock. Or are you just assuming that, because I said that =is
what
blocks an IX lock?
-- HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
Yup cant really find anything due to it being orphaned, but =found
something
else for other spids when i ran sp_lock
Came across a type of "APP" and resource as "DNSQ4b945027" as an
example
for some spids.. Any idea what types of locks these are
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
sp_lock shows you who's holding what locks.
-- HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
So how can I find out whats holding an X lock to this table =... I
mean
which
spid ? if you've read my previous thread, Im having some orphaned
connections and these spids that Im trying to kill have IX locks
on
the
table and wouldnt kill..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
Hi Hassan
IX lock on the table means that someone has X lock on a row or a
page.
So
you can do any modifications on other rows or pages. You just
can't
do
anything that requires X lock on the whole table. X lock is
incompatible
with IX, but IX can be combined with another IX (two =processes
with
X
lock
on separate rows each have IX on the table).
-- HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
If I have an IX lock on a table, what kind of DMLs am I
restricted
doing
?
=
--=_NextPart_000_000D_01C514E6.A3E1B940
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Not hiding anything.. And yes its weird =and I know a restart of the SQL Services would fix it but I'd like to know more =about the internals on the PSS Structure that holds this info and that a kill =command cannot get to it
"Mike Hodgson" wrote in message news:uxaP6LLFFHA.624@.T=K2MSFTNGP15.phx.gbl...You can't have just an IX lock on a table (can you?). An intent lock =is always as a result of a more granular lock. So if you have an =TAB IX lock then you must also have, for spid 94, a PAG X lock, a KEY X lock =or a RID X lock. Are there other locks on that spid that you're not =showing us or is that it?To answer your original question, if there is an =existing IX lock on a table, any lock request other than an intent lock (IX or =IS) on the table will be blocked. So you can get a S, U or X lock on a =page or row in the table (assuming it's not the page or row that spid 94 is X locking), which will give you an IS or IX lock on the table. So =that means you can do a SELECT, INSERT, UPDATE or DELETE on the table as =long as you only need to lock rows or pages in the table and not the whole table.
--mike =hodgson | database =administrator | mallesons =stephen jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.nospam.com | W http://www.mallesons.com Hassan wrote: sp_lock
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- -- 94 8 0 0 DB S GRANT
94 8 142321797 0 TAB IX GRANT
"Kalen Delaney" wrote in message
news:%23h3yq8KFFHA.=1932@.TK2MSFTNGP14.phx.gbl...
How are you seeing the IX =lock? Run sp_lock, and look at all the rows for
the same spid that has the IX lock. In fact, you can pass the spid as an
argument to sp_lock.
-- HTH
--
Kalen Delaney
SQL Server MVP
http://www.SolidQualityLearning.com">www.SolidQualityLearning.com="Hassan" =wrote in message
news:O5e0dqJFFHA.2296=@.TK2MSFTNGP15.phx.gbl...
Yes I have an IX lock on =a table and you mentioned that I would see if
if
one has an X lock on a =page or a row. But could not find anything
"Kalen Delaney" wrote in message
news:udWI7vIFFHA.3928=@.TK2MSFTNGP15.phx.gbl...
Those are Application =locks, defined by the user (or application). You
can
read about Application Lock in the Books Online.
So how do you know someone is holding an X lock on a table, if you
can't
see
it in sp_lock. Or are =you just assuming that, because I said that is
what
blocks an IX lock?
-- HTH
--
Kalen Delaney
SQL Server MVP
http://www.SolidQualityLearning.com">www.SolidQualityLearning.com="Hassan" =wrote in message
news:O54vrfIFFHA.3336=@.TK2MSFTNGP10.phx.gbl...
Yup cant really find =anything due to it being orphaned, but found
something
else for other spids when i ran sp_lock
Came across a type of "APP" and resource as "DNSQ4b945027" as an
example
for some spids.. Any =idea what types of locks these are
"Kalen Delaney" wrote in message
news:%23Z9XiyHFFHA.=3384@.tk2msftngp13.phx.gbl...
sp_lock shows you =who's holding what locks.
-- HTH
--
Kalen Delaney
SQL Server MVP
http://www.SolidQualityLearning.com">www.SolidQualityLearning.com="Hassan" =wrote in message
news:uh4d0rHFFHA.2180=@.TK2MSFTNGP10.phx.gbl...
So how can I find =out whats holding an X lock to this table ... I
=mean
which
spid ? if you've read my previous thread, Im having some orphaned
connections and these spids that Im trying to kill have IX locks
=on
the
table and wouldnt =kill..
"Kalen Delaney" wrote in message
news:OUsmFVHFFHA.2460=@.TK2MSFTNGP09.phx.gbl...
Hi Hassan
IX lock on the table means that someone has X lock on a row or a
=page.
So
you can do any =modifications on other rows or pages. You just
=can't
do
anything that =requires X lock on the whole table. X lock is
incompatible
with IX, but IX = can be combined with another IX (two processes
with
=X
lock
on separate =rows each have IX on the table).
-- HTH
--
Kalen Delaney
SQL Server MVP
http://www.SolidQualityLearning.com">www.SolidQualityLearning.com="Hassan" =wrote in message
news:eagRuoGFFHA.3888=@.TK2MSFTNGP12.phx.gbl...
If I have an =IX lock on a table, what kind of DMLs am I
restricted
doing
?
= = = =
--=_NextPart_000_000D_01C514E6.A3E1B940--sql
IX lock on the table means that someone has X lock on a row or a page. So
you can do any modifications on other rows or pages. You just can't do
anything that requires X lock on the whole table. X lock is incompatible
with IX, but IX can be combined with another IX (two processes with X lock
on separate rows each have IX on the table).
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
> If I have an IX lock on a table, what kind of DMLs am I restricted doing ?
>|||So how can I find out whats holding an X lock to this table ... I mean which
spid ? if you've read my previous thread, Im having some orphaned
connections and these spids that Im trying to kill have IX locks on the
table and wouldnt kill..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> Hi Hassan
> IX lock on the table means that someone has X lock on a row or a page. So
> you can do any modifications on other rows or pages. You just can't do
> anything that requires X lock on the whole table. X lock is incompatible
> with IX, but IX can be combined with another IX (two processes with X
lock
> on separate rows each have IX on the table).
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
> > If I have an IX lock on a table, what kind of DMLs am I restricted doing
?
> >
> >
>|||sp_lock shows you who's holding what locks.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> So how can I find out whats holding an X lock to this table ... I mean
> which
> spid ? if you've read my previous thread, Im having some orphaned
> connections and these spids that Im trying to kill have IX locks on the
> table and wouldnt kill..
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
>> Hi Hassan
>> IX lock on the table means that someone has X lock on a row or a page. So
>> you can do any modifications on other rows or pages. You just can't do
>> anything that requires X lock on the whole table. X lock is incompatible
>> with IX, but IX can be combined with another IX (two processes with X
> lock
>> on separate rows each have IX on the table).
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
>> > If I have an IX lock on a table, what kind of DMLs am I restricted
>> > doing
> ?
>> >
>> >
>>
>|||Yup cant really find anything due to it being orphaned, but found something
else for other spids when i ran sp_lock
Came across a type of "APP" and resource as "DNSQ4b945027" as an example
for some spids.. Any idea what types of locks these are
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
> sp_lock shows you who's holding what locks.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> > So how can I find out whats holding an X lock to this table ... I mean
> > which
> > spid ? if you've read my previous thread, Im having some orphaned
> > connections and these spids that Im trying to kill have IX locks on the
> > table and wouldnt kill..
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> >> Hi Hassan
> >>
> >> IX lock on the table means that someone has X lock on a row or a page.
So
> >> you can do any modifications on other rows or pages. You just can't do
> >> anything that requires X lock on the whole table. X lock is
incompatible
> >> with IX, but IX can be combined with another IX (two processes with X
> > lock
> >> on separate rows each have IX on the table).
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> >> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
> >> > If I have an IX lock on a table, what kind of DMLs am I restricted
> >> > doing
> > ?
> >> >
> >> >
> >>
> >>
> >
> >
>|||Those are Application locks, defined by the user (or application). You can
read about Application Lock in the Books Online.
So how do you know someone is holding an X lock on a table, if you can't see
it in sp_lock. Or are you just assuming that, because I said that is what
blocks an IX lock?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Yup cant really find anything due to it being orphaned, but found
> something
> else for other spids when i ran sp_lock
> Came across a type of "APP" and resource as "DNSQ4b945027" as an example
> for some spids.. Any idea what types of locks these are
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
>> sp_lock shows you who's holding what locks.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
>> > So how can I find out whats holding an X lock to this table ... I mean
>> > which
>> > spid ? if you've read my previous thread, Im having some orphaned
>> > connections and these spids that Im trying to kill have IX locks on the
>> > table and wouldnt kill..
>> >
>> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> > news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
>> >> Hi Hassan
>> >>
>> >> IX lock on the table means that someone has X lock on a row or a page.
> So
>> >> you can do any modifications on other rows or pages. You just can't do
>> >> anything that requires X lock on the whole table. X lock is
> incompatible
>> >> with IX, but IX can be combined with another IX (two processes with X
>> > lock
>> >> on separate rows each have IX on the table).
>> >>
>> >> --
>> >> HTH
>> >> --
>> >> Kalen Delaney
>> >> SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> >> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
>> >> > If I have an IX lock on a table, what kind of DMLs am I restricted
>> >> > doing
>> > ?
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Yes I have an IX lock on a table and you mentioned that I would see if if
one has an X lock on a page or a row. But could not find anything
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
> Those are Application locks, defined by the user (or application). You can
> read about Application Lock in the Books Online.
> So how do you know someone is holding an X lock on a table, if you can't
see
> it in sp_lock. Or are you just assuming that, because I said that is what
> blocks an IX lock?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> > Yup cant really find anything due to it being orphaned, but found
> > something
> > else for other spids when i ran sp_lock
> >
> > Came across a type of "APP" and resource as "DNSQ4b945027" as an
example
> > for some spids.. Any idea what types of locks these are
> >
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
> >> sp_lock shows you who's holding what locks.
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> >> news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> >> > So how can I find out whats holding an X lock to this table ... I
mean
> >> > which
> >> > spid ? if you've read my previous thread, Im having some orphaned
> >> > connections and these spids that Im trying to kill have IX locks on
the
> >> > table and wouldnt kill..
> >> >
> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> >> > news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> >> >> Hi Hassan
> >> >>
> >> >> IX lock on the table means that someone has X lock on a row or a
page.
> > So
> >> >> you can do any modifications on other rows or pages. You just can't
do
> >> >> anything that requires X lock on the whole table. X lock is
> > incompatible
> >> >> with IX, but IX can be combined with another IX (two processes with
X
> >> > lock
> >> >> on separate rows each have IX on the table).
> >> >>
> >> >> --
> >> >> HTH
> >> >> --
> >> >> Kalen Delaney
> >> >> SQL Server MVP
> >> >> www.SolidQualityLearning.com
> >> >>
> >> >>
> >> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> >> >> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
> >> >> > If I have an IX lock on a table, what kind of DMLs am I restricted
> >> >> > doing
> >> > ?
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||How are you seeing the IX lock? Run sp_lock, and look at all the rows for
the same spid that has the IX lock. In fact, you can pass the spid as an
argument to sp_lock.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl...
> Yes I have an IX lock on a table and you mentioned that I would see if if
> one has an X lock on a page or a row. But could not find anything
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
>> Those are Application locks, defined by the user (or application). You
>> can
>> read about Application Lock in the Books Online.
>> So how do you know someone is holding an X lock on a table, if you can't
> see
>> it in sp_lock. Or are you just assuming that, because I said that is what
>> blocks an IX lock?
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
>> > Yup cant really find anything due to it being orphaned, but found
>> > something
>> > else for other spids when i ran sp_lock
>> >
>> > Came across a type of "APP" and resource as "DNSQ4b945027" as an
> example
>> > for some spids.. Any idea what types of locks these are
>> >
>> >
>> >
>> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> > news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
>> >> sp_lock shows you who's holding what locks.
>> >>
>> >> --
>> >> HTH
>> >> --
>> >> Kalen Delaney
>> >> SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> >> news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
>> >> > So how can I find out whats holding an X lock to this table ... I
> mean
>> >> > which
>> >> > spid ? if you've read my previous thread, Im having some orphaned
>> >> > connections and these spids that Im trying to kill have IX locks on
> the
>> >> > table and wouldnt kill..
>> >> >
>> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> >> > news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
>> >> >> Hi Hassan
>> >> >>
>> >> >> IX lock on the table means that someone has X lock on a row or a
> page.
>> > So
>> >> >> you can do any modifications on other rows or pages. You just can't
> do
>> >> >> anything that requires X lock on the whole table. X lock is
>> > incompatible
>> >> >> with IX, but IX can be combined with another IX (two processes
>> >> >> with
> X
>> >> > lock
>> >> >> on separate rows each have IX on the table).
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >> --
>> >> >> Kalen Delaney
>> >> >> SQL Server MVP
>> >> >> www.SolidQualityLearning.com
>> >> >>
>> >> >>
>> >> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> >> >> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
>> >> >> > If I have an IX lock on a table, what kind of DMLs am I
>> >> >> > restricted
>> >> >> > doing
>> >> > ?
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||sp_lock <orphaned spid>
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
94 8 0 0 DB S GRANT
94 8 142321797 0 TAB IX GRANT
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl...
> How are you seeing the IX lock? Run sp_lock, and look at all the rows for
> the same spid that has the IX lock. In fact, you can pass the spid as an
> argument to sp_lock.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl...
> > Yes I have an IX lock on a table and you mentioned that I would see if
if
> > one has an X lock on a page or a row. But could not find anything
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
> >> Those are Application locks, defined by the user (or application). You
> >> can
> >> read about Application Lock in the Books Online.
> >>
> >> So how do you know someone is holding an X lock on a table, if you
can't
> > see
> >> it in sp_lock. Or are you just assuming that, because I said that is
what
> >> blocks an IX lock?
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> >> news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> >> > Yup cant really find anything due to it being orphaned, but found
> >> > something
> >> > else for other spids when i ran sp_lock
> >> >
> >> > Came across a type of "APP" and resource as "DNSQ4b945027" as an
> > example
> >> > for some spids.. Any idea what types of locks these are
> >> >
> >> >
> >> >
> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> >> > news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
> >> >> sp_lock shows you who's holding what locks.
> >> >>
> >> >> --
> >> >> HTH
> >> >> --
> >> >> Kalen Delaney
> >> >> SQL Server MVP
> >> >> www.SolidQualityLearning.com
> >> >>
> >> >>
> >> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> >> >> news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> >> >> > So how can I find out whats holding an X lock to this table ... I
> > mean
> >> >> > which
> >> >> > spid ? if you've read my previous thread, Im having some orphaned
> >> >> > connections and these spids that Im trying to kill have IX locks
on
> > the
> >> >> > table and wouldnt kill..
> >> >> >
> >> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> >> >> > news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
> >> >> >> Hi Hassan
> >> >> >>
> >> >> >> IX lock on the table means that someone has X lock on a row or a
> > page.
> >> > So
> >> >> >> you can do any modifications on other rows or pages. You just
can't
> > do
> >> >> >> anything that requires X lock on the whole table. X lock is
> >> > incompatible
> >> >> >> with IX, but IX can be combined with another IX (two processes
> >> >> >> with
> > X
> >> >> > lock
> >> >> >> on separate rows each have IX on the table).
> >> >> >>
> >> >> >> --
> >> >> >> HTH
> >> >> >> --
> >> >> >> Kalen Delaney
> >> >> >> SQL Server MVP
> >> >> >> www.SolidQualityLearning.com
> >> >> >>
> >> >> >>
> >> >> >> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> >> >> >> news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
> >> >> >> > If I have an IX lock on a table, what kind of DMLs am I
> >> >> >> > restricted
> >> >> >> > doing
> >> >> > ?
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||This is a multi-part message in MIME format.
--060208030708050403020006
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
You can't have just an IX lock on a table (can you?). An intent lock is
always as a result of a more granular lock. So if you have an TAB IX
lock then you must also have, for spid 94, a PAG X lock, a KEY X lock or
a RID X lock. Are there other locks on that spid that you're not
showing us or is that it?
To answer your original question, if there is an existing IX lock on a
table, any lock request other than an intent lock (IX or IS) on the
table will be blocked. So you can get a S, U or X lock on a page or row
in the table (assuming it's not the page or row that spid 94 is X
locking), which will give you an IS or IX lock on the table. So that
means you can do a SELECT, INSERT, UPDATE or DELETE on the table as long
as you only need to lock rows or pages in the table and not the whole table.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:
>sp_lock <orphaned spid>
>spid dbid ObjId IndId Type Resource Mode Status
>-- -- -- -- -- -- -- --
>94 8 0 0 DB S GRANT
>94 8 142321797 0 TAB IX GRANT
>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl...
>
>>How are you seeing the IX lock? Run sp_lock, and look at all the rows for
>>the same spid that has the IX lock. In fact, you can pass the spid as an
>>argument to sp_lock.
>>--
>>HTH
>>--
>>Kalen Delaney
>>SQL Server MVP
>>www.SolidQualityLearning.com
>>
>>"Hassan" <fatima_ja@.hotmail.com> wrote in message
>>news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl...
>>
>>Yes I have an IX lock on a table and you mentioned that I would see if
>>
>if
>
>>one has an X lock on a page or a row. But could not find anything
>>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>>news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
>>
>>Those are Application locks, defined by the user (or application). You
>>can
>>read about Application Lock in the Books Online.
>>So how do you know someone is holding an X lock on a table, if you
>>
>can't
>
>>see
>>
>>it in sp_lock. Or are you just assuming that, because I said that is
>>
>what
>
>>blocks an IX lock?
>>--
>>HTH
>>--
>>Kalen Delaney
>>SQL Server MVP
>>www.SolidQualityLearning.com
>>
>>"Hassan" <fatima_ja@.hotmail.com> wrote in message
>>news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
>>
>>Yup cant really find anything due to it being orphaned, but found
>>something
>>else for other spids when i ran sp_lock
>>Came across a type of "APP" and resource as "DNSQ4b945027" as an
>>
>>example
>>
>>for some spids.. Any idea what types of locks these are
>>
>>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>>news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
>>
>>sp_lock shows you who's holding what locks.
>>--
>>HTH
>>--
>>Kalen Delaney
>>SQL Server MVP
>>www.SolidQualityLearning.com
>>
>>"Hassan" <fatima_ja@.hotmail.com> wrote in message
>>news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
>>
>>>So how can I find out whats holding an X lock to this table ... I
>>>
>>>
>>mean
>>
>>>which
>>>spid ? if you've read my previous thread, Im having some orphaned
>>>connections and these spids that Im trying to kill have IX locks
>>>
>>>
>on
>
>>the
>>
>>>table and wouldnt kill..
>>>
>>>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>>>news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
>>>
>>>
>>>Hi Hassan
>>>
>>>IX lock on the table means that someone has X lock on a row or a
>>>
>>>
>>page.
>>
>>So
>>
>>>you can do any modifications on other rows or pages. You just
>>>
>>>
>can't
>
>>do
>>
>>>anything that requires X lock on the whole table. X lock is
>>>
>>>
>>incompatible
>>
>>>with IX, but IX can be combined with another IX (two processes
>>>with
>>>
>>>
>>X
>>
>>>lock
>>>
>>>
>>>on separate rows each have IX on the table).
>>>
>>>--
>>>HTH
>>>--
>>>Kalen Delaney
>>>SQL Server MVP
>>>www.SolidQualityLearning.com
>>>
>>>
>>>"Hassan" <fatima_ja@.hotmail.com> wrote in message
>>>news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
>>>
>>>
>>>If I have an IX lock on a table, what kind of DMLs am I
>>>restricted
>>>doing
>>>
>>>
>>>?
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
>
--060208030708050403020006
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You can't have just an IX lock on a table (can you?). An intent
lock is always as a result of a more granular lock. So if you have an
TAB IX lock then you must also have, for spid 94, a PAG X lock, a KEY X
lock or a RID X lock. Are there other locks on that spid that you're
not showing us or is that it?<br>
<br>
To answer your original question, if there is an existing IX lock on a
table, any lock request other than an intent lock (IX or IS) on the
table will be blocked. So you can get a S, U or X lock on a page or
row in the table (assuming it's not the page or row that spid 94 is X
locking), which will give you an IS or IX lock on the table. So that
means you can do a SELECT, INSERT, UPDATE or DELETE on the table as
long as you only need to lock rows or pages in the table and not the
whole table.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Hassan wrote:
<blockquote cite="midO4wukBLFFHA.2832@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">sp_lock <orphaned spid>
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
94 8 0 0 DB S GRANT
94 8 142321797 0 TAB IX GRANT
"Kalen Delaney" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl">news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">How are you seeing the IX lock? Run sp_lock, and look at all the rows for
the same spid that has the IX lock. In fact, you can pass the spid as an
argument to sp_lock.
--
HTH
--
Kalen Delaney
SQL Server MVP
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.SolidQualityLearning.com">www.SolidQualityLearning.com</a>
"Hassan" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:fatima_ja@.hotmail.com"><fatima_ja@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl">news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Yes I have an IX lock on a table and you mentioned that I would see if
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->if
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">one has an X lock on a page or a row. But could not find anything
"Kalen Delaney" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl">news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Those are Application locks, defined by the user (or application). You
can
read about Application Lock in the Books Online.
So how do you know someone is holding an X lock on a table, if you
</pre>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!-->can't
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">see
</pre>
<blockquote type="cite">
<pre wrap="">it in sp_lock. Or are you just assuming that, because I said that is
</pre>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!-->what
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">blocks an IX lock?
--
HTH
--
Kalen Delaney
SQL Server MVP
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.SolidQualityLearning.com">www.SolidQualityLearning.com</a>
"Hassan" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:fatima_ja@.hotmail.com"><fatima_ja@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl">news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Yup cant really find anything due to it being orphaned, but found
something
else for other spids when i ran sp_lock
Came across a type of "APP" and resource as "DNSQ4b945027" as an
</pre>
</blockquote>
</blockquote>
<pre wrap="">example
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">for some spids.. Any idea what types of locks these are
"Kalen Delaney" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl">news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">sp_lock shows you who's holding what locks.
--
HTH
--
Kalen Delaney
SQL Server MVP
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.SolidQualityLearning.com">www.SolidQualityLearning.com</a>
"Hassan" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:fatima_ja@.hotmail.com"><fatima_ja@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl">news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">So how can I find out whats holding an X lock to this table ... I
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="">mean
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">which
spid ? if you've read my previous thread, Im having some orphaned
connections and these spids that Im trying to kill have IX locks
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!-->on
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">the
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">table and wouldnt kill..
"Kalen Delaney" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:replies@.public_newsgroups.com"><replies@.public_newsgroups.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl">news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi Hassan
IX lock on the table means that someone has X lock on a row or a
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="">page.
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">So
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">you can do any modifications on other rows or pages. You just
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap=""><!-->can't
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">do
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">anything that requires X lock on the whole table. X lock is
</pre>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="">incompatible
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">with IX, but IX can be combined with another IX (two processes
with
</pre>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="">X
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">lock
</pre>
<blockquote type="cite">
<pre wrap="">on separate rows each have IX on the table).
--
HTH
--
Kalen Delaney
SQL Server MVP
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.solidqualitylearning.com</a>">http://www.SolidQualityLearning.com">www.SolidQualityLearning.com</a>
"Hassan" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:fatima_ja@.hotmail.com"><fatima_ja@.hotmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl">news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">If I have an IX lock on a table, what kind of DMLs am I
restricted
doing
</pre>
</blockquote>
</blockquote>
<pre wrap="">?
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--060208030708050403020006--|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C514E6.A3E1B940
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Not hiding anything.. And yes its weird and I know a restart of the SQL =Services would fix it but I'd like to know more about the internals on =the PSS Structure that holds this info and that a kill command cannot =get to it
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:uxaP6LLFFHA.624@.TK2MSFTNGP15.phx.gbl...
You can't have just an IX lock on a table (can you?). An intent lock =is always as a result of a more granular lock. So if you have an TAB IX =lock then you must also have, for spid 94, a PAG X lock, a KEY X lock or =a RID X lock. Are there other locks on that spid that you're not =showing us or is that it?
To answer your original question, if there is an existing IX lock on a =table, any lock request other than an intent lock (IX or IS) on the =table will be blocked. So you can get a S, U or X lock on a page or row =in the table (assuming it's not the page or row that spid 94 is X =locking), which will give you an IS or IX lock on the table. So that =means you can do a SELECT, INSERT, UPDATE or DELETE on the table as long =as you only need to lock rows or pages in the table and not the whole =table.
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W =http://www.mallesons.com=20
Hassan wrote: sp_lock <orphaned spid>
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- -- 94 8 0 0 DB S GRANT
94 8 142321797 0 TAB IX GRANT
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23h3yq8KFFHA.1932@.TK2MSFTNGP14.phx.gbl...
How are you seeing the IX lock? Run sp_lock, and look at all the rows =for
the same spid that has the IX lock. In fact, you can pass the spid as an
argument to sp_lock.
-- HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O5e0dqJFFHA.2296@.TK2MSFTNGP15.phx.gbl...
Yes I have an IX lock on a table and you mentioned that I would see =if
if
one has an X lock on a page or a row. But could not find anything
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:udWI7vIFFHA.3928@.TK2MSFTNGP15.phx.gbl...
Those are Application locks, defined by the user (or application). =You
can
read about Application Lock in the Books Online.
So how do you know someone is holding an X lock on a table, if you
can't
see
it in sp_lock. Or are you just assuming that, because I said that =is
what
blocks an IX lock?
-- HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O54vrfIFFHA.3336@.TK2MSFTNGP10.phx.gbl...
Yup cant really find anything due to it being orphaned, but =found
something
else for other spids when i ran sp_lock
Came across a type of "APP" and resource as "DNSQ4b945027" as an
example
for some spids.. Any idea what types of locks these are
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Z9XiyHFFHA.3384@.tk2msftngp13.phx.gbl...
sp_lock shows you who's holding what locks.
-- HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uh4d0rHFFHA.2180@.TK2MSFTNGP10.phx.gbl...
So how can I find out whats holding an X lock to this table =... I
mean
which
spid ? if you've read my previous thread, Im having some orphaned
connections and these spids that Im trying to kill have IX locks
on
the
table and wouldnt kill..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OUsmFVHFFHA.2460@.TK2MSFTNGP09.phx.gbl...
Hi Hassan
IX lock on the table means that someone has X lock on a row or a
page.
So
you can do any modifications on other rows or pages. You just
can't
do
anything that requires X lock on the whole table. X lock is
incompatible
with IX, but IX can be combined with another IX (two =processes
with
X
lock
on separate rows each have IX on the table).
-- HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eagRuoGFFHA.3888@.TK2MSFTNGP12.phx.gbl...
If I have an IX lock on a table, what kind of DMLs am I
restricted
doing
?
=
--=_NextPart_000_000D_01C514E6.A3E1B940
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Not hiding anything.. And yes its weird =and I know a restart of the SQL Services would fix it but I'd like to know more =about the internals on the PSS Structure that holds this info and that a kill =command cannot get to it
"Mike Hodgson"
--mike =hodgson | database =administrator | mallesons =stephen jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.nospam.com | W http://www.mallesons.com Hassan wrote: sp_lock
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- -- 94 8 0 0 DB S GRANT
94 8 142321797 0 TAB IX GRANT
"Kalen Delaney" wrote in message
news:%23h3yq8KFFHA.=1932@.TK2MSFTNGP14.phx.gbl...
How are you seeing the IX =lock? Run sp_lock, and look at all the rows for
the same spid that has the IX lock. In fact, you can pass the spid as an
argument to sp_lock.
-- HTH
--
Kalen Delaney
SQL Server MVP
http://www.SolidQualityLearning.com">www.SolidQualityLearning.com="Hassan" =wrote in message
news:O5e0dqJFFHA.2296=@.TK2MSFTNGP15.phx.gbl...
Yes I have an IX lock on =a table and you mentioned that I would see if
if
one has an X lock on a =page or a row. But could not find anything
"Kalen Delaney" wrote in message
news:udWI7vIFFHA.3928=@.TK2MSFTNGP15.phx.gbl...
Those are Application =locks, defined by the user (or application). You
can
read about Application Lock in the Books Online.
So how do you know someone is holding an X lock on a table, if you
can't
see
it in sp_lock. Or are =you just assuming that, because I said that is
what
blocks an IX lock?
-- HTH
--
Kalen Delaney
SQL Server MVP
http://www.SolidQualityLearning.com">www.SolidQualityLearning.com="Hassan" =wrote in message
news:O54vrfIFFHA.3336=@.TK2MSFTNGP10.phx.gbl...
Yup cant really find =anything due to it being orphaned, but found
something
else for other spids when i ran sp_lock
Came across a type of "APP" and resource as "DNSQ4b945027" as an
example
for some spids.. Any =idea what types of locks these are
"Kalen Delaney" wrote in message
news:%23Z9XiyHFFHA.=3384@.tk2msftngp13.phx.gbl...
sp_lock shows you =who's holding what locks.
-- HTH
--
Kalen Delaney
SQL Server MVP
http://www.SolidQualityLearning.com">www.SolidQualityLearning.com="Hassan" =wrote in message
news:uh4d0rHFFHA.2180=@.TK2MSFTNGP10.phx.gbl...
So how can I find =out whats holding an X lock to this table ... I
=mean
which
spid ? if you've read my previous thread, Im having some orphaned
connections and these spids that Im trying to kill have IX locks
=on
the
table and wouldnt =kill..
"Kalen Delaney" wrote in message
news:OUsmFVHFFHA.2460=@.TK2MSFTNGP09.phx.gbl...
Hi Hassan
IX lock on the table means that someone has X lock on a row or a
=page.
So
you can do any =modifications on other rows or pages. You just
=can't
do
anything that =requires X lock on the whole table. X lock is
incompatible
with IX, but IX = can be combined with another IX (two processes
with
=X
lock
on separate =rows each have IX on the table).
-- HTH
--
Kalen Delaney
SQL Server MVP
http://www.SolidQualityLearning.com">www.SolidQualityLearning.com="Hassan" =wrote in message
news:eagRuoGFFHA.3888=@.TK2MSFTNGP12.phx.gbl...
If I have an =IX lock on a table, what kind of DMLs am I
restricted
doing
?
= = = =
--=_NextPart_000_000D_01C514E6.A3E1B940--sql
Subscribe to:
Posts (Atom)