LOCK confusion

I'm reading Delaney's Inside SQL 2000 book & BOL right now and getting
more confused....my question deals with Locking. I have a simple one
column table with 20000 records or so. I want to in this order:
A) restrict/halt all insert/update activity on this table (the only
inserts that come to this table are from a trigger on another table)
B) move the contents over to another table
C) delete the contents
D) Open the table back up for the application to use
B & C are very basic & I'm fine with INSERT, DELETE, etc. I've never
used the locking functionality, however. What locking services does
SQL automatically take care of, and what will I have to specify in my
example?
Well, with your fairly simple example you can solve the problem simply
by doing all the operations in an explicit transaction and specifying a
locking hint or two.
The HOLDLOCK locking hint will essentially put the session into
SERIALIZABLE isolation level, which means locks are held until the end
of the transaction. So if you also specify a TABLOCKX hint, then the
whole table will be locked with an exclusive lock, meaning no other SQL
process (SPID) will be able to acquire any kind of lock on any part of
that table until the transaction has been committed (or rolled back).
So the batch would be something like:
BEGIN TRAN
INSERT INTO MyArchiveTable (col1, col2, ...)
SELECT col1, col2, ... FROM MyTable *WITH (HOLDLOCK, TABLOCKX)*
DELETE MyTable
COMMIT TRAN
In fact, you probably wouldn't even need a TABLOCKX, a TABLOCK would
probably do because INSERTs & UPDATEs on the table will require an IX
lock on the table resource and an IX lock is incompatible with the S
lock on the table, so it will wait until the S lock has been released,
which will be when you commit the transaction.
(I hope this is right - my reference to all this (Inside SQL Server
2000) is sitting at home at the moment, so this is from memory.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
unc27932@.yahoo.com wrote:

>I'm reading Delaney's Inside SQL 2000 book & BOL right now and getting
>more confused....my question deals with Locking. I have a simple one
>column table with 20000 records or so. I want to in this order:
>A) restrict/halt all insert/update activity on this table (the only
>inserts that come to this table are from a trigger on another table)
>B) move the contents over to another table
>C) delete the contents
>D) Open the table back up for the application to use
>B & C are very basic & I'm fine with INSERT, DELETE, etc. I've never
>used the locking functionality, however. What locking services does
>SQL automatically take care of, and what will I have to specify in my
>example?
>
>
|||I guess I'm not getting two things here...
A) the difference between TABLOCK and TABLOCKX. Does TABLOCK allow
reads on the table, and just not insert/updates? And TABLOCKX allow
nothing at all?
B) HOLDLOCK & its purpose. If I specify TABLOCK or TABLOCKX as a hint,
why is HOLDLOCK necessary?
Mike Hodgson wrote:[vbcol=seagreen]
> Well, with your fairly simple example you can solve the problem simply
> by doing all the operations in an explicit transaction and specifying a
> locking hint or two.
> The HOLDLOCK locking hint will essentially put the session into
> SERIALIZABLE isolation level, which means locks are held until the end
> of the transaction. So if you also specify a TABLOCKX hint, then the
> whole table will be locked with an exclusive lock, meaning no other SQL
> process (SPID) will be able to acquire any kind of lock on any part of
> that table until the transaction has been committed (or rolled back).
> So the batch would be something like:
> BEGIN TRAN
> INSERT INTO MyArchiveTable (col1, col2, ...)
> SELECT col1, col2, ... FROM MyTable *WITH (HOLDLOCK, TABLOCKX)*
> DELETE MyTable
> COMMIT TRAN
> In fact, you probably wouldn't even need a TABLOCKX, a TABLOCK would
> probably do because INSERTs & UPDATEs on the table will require an IX
> lock on the table resource and an IX lock is incompatible with the S
> lock on the table, so it will wait until the S lock has been released,
> which will be when you commit the transaction.
> (I hope this is right - my reference to all this (Inside SQL Server
> 2000) is sitting at home at the moment, so this is from memory.)
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> unc27932@.yahoo.com wrote:
|||OK - I think I have it. TABLOCK will allow others to read the table ,
but not add any more records. Whereas TABLOCKX won't allow anyone else
to even read the table, let alone, update/insert.
And HOLDLOCK is used to hold the shared lock through the end of the
transaction, not just the data operation/read/whatever.
Is this right? Opinions?
So what happens if a user attempts to insert a row at the moment I have
it locked? Does their application just wait a few seconds, and then
SQL lets them back into the table after the lock is released? Or will
they get an error?
|||Yes that is right. If you have a lock on the table and someone attempts to
insert a new row (or delete, update etc) they will be blocked (halted state)
until one of the following occurs. Either their connection times out or the
lock gets released. The timeout is dependant on the connection settings as
to how long it waits.
Andrew J. Kelly SQL MVP
<unc27932@.yahoo.com> wrote in message
news:1123087028.818016.284860@.o13g2000cwo.googlegr oups.com...
> OK - I think I have it. TABLOCK will allow others to read the table ,
> but not add any more records. Whereas TABLOCKX won't allow anyone else
> to even read the table, let alone, update/insert.
> And HOLDLOCK is used to hold the shared lock through the end of the
> transaction, not just the data operation/read/whatever.
> Is this right? Opinions?
> So what happens if a user attempts to insert a row at the moment I have
> it locked? Does their application just wait a few seconds, and then
> SQL lets them back into the table after the lock is released? Or will
> they get an error?
>

LOCK confusion

I'm reading Delaney's Inside SQL 2000 book & BOL right now and getting
more confused....my question deals with Locking. I have a simple one
column table with 20000 records or so. I want to in this order:
A) restrict/halt all insert/update activity on this table (the only
inserts that come to this table are from a trigger on another table)
B) move the contents over to another table
C) delete the contents
D) Open the table back up for the application to use
B & C are very basic & I'm fine with INSERT, DELETE, etc. I've never
used the locking functionality, however. What locking services does
SQL automatically take care of, and what will I have to specify in my
example?Well, with your fairly simple example you can solve the problem simply
by doing all the operations in an explicit transaction and specifying a
locking hint or two.
The HOLDLOCK locking hint will essentially put the session into
SERIALIZABLE isolation level, which means locks are held until the end
of the transaction. So if you also specify a TABLOCKX hint, then the
whole table will be locked with an exclusive lock, meaning no other SQL
process (SPID) will be able to acquire any kind of lock on any part of
that table until the transaction has been committed (or rolled back).
So the batch would be something like:
BEGIN TRAN
INSERT INTO MyArchiveTable (col1, col2, ...)
SELECT col1, col2, ... FROM MyTable *WITH (HOLDLOCK, TABLOCKX)*
DELETE MyTable
COMMIT TRAN
In fact, you probably wouldn't even need a TABLOCKX, a TABLOCK would
probably do because INSERTs & UPDATEs on the table will require an IX
lock on the table resource and an IX lock is incompatible with the S
lock on the table, so it will wait until the S lock has been released,
which will be when you commit the transaction.
(I hope this is right - my reference to all this (Inside SQL Server
2000) is sitting at home at the moment, so this is from memory.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
unc27932@.yahoo.com wrote:

>I'm reading Delaney's Inside SQL 2000 book & BOL right now and getting
>more confused....my question deals with Locking. I have a simple one
>column table with 20000 records or so. I want to in this order:
>A) restrict/halt all insert/update activity on this table (the only
>inserts that come to this table are from a trigger on another table)
>B) move the contents over to another table
>C) delete the contents
>D) Open the table back up for the application to use
>B & C are very basic & I'm fine with INSERT, DELETE, etc. I've never
>used the locking functionality, however. What locking services does
>SQL automatically take care of, and what will I have to specify in my
>example?
>
>|||I guess I'm not getting two things here...
A) the difference between TABLOCK and TABLOCKX. Does TABLOCK allow
reads on the table, and just not insert/updates? And TABLOCKX allow
nothing at all?
B) HOLDLOCK & its purpose. If I specify TABLOCK or TABLOCKX as a hint,
why is HOLDLOCK necessary?
Mike Hodgson wrote:[vbcol=seagreen]
> Well, with your fairly simple example you can solve the problem simply
> by doing all the operations in an explicit transaction and specifying a
> locking hint or two.
> The HOLDLOCK locking hint will essentially put the session into
> SERIALIZABLE isolation level, which means locks are held until the end
> of the transaction. So if you also specify a TABLOCKX hint, then the
> whole table will be locked with an exclusive lock, meaning no other SQL
> process (SPID) will be able to acquire any kind of lock on any part of
> that table until the transaction has been committed (or rolled back).
> So the batch would be something like:
> BEGIN TRAN
> INSERT INTO MyArchiveTable (col1, col2, ...)
> SELECT col1, col2, ... FROM MyTable *WITH (HOLDLOCK, TABLOCKX)*
> DELETE MyTable
> COMMIT TRAN
> In fact, you probably wouldn't even need a TABLOCKX, a TABLOCK would
> probably do because INSERTs & UPDATEs on the table will require an IX
> lock on the table resource and an IX lock is incompatible with the S
> lock on the table, so it will wait until the S lock has been released,
> which will be when you commit the transaction.
> (I hope this is right - my reference to all this (Inside SQL Server
> 2000) is sitting at home at the moment, so this is from memory.)
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> unc27932@.yahoo.com wrote:
>|||OK - I think I have it. TABLOCK will allow others to read the table ,
but not add any more records. Whereas TABLOCKX won't allow anyone else
to even read the table, let alone, update/insert.
And HOLDLOCK is used to hold the shared lock through the end of the
transaction, not just the data operation/read/whatever.
Is this right? Opinions?
So what happens if a user attempts to insert a row at the moment I have
it locked? Does their application just wait a few seconds, and then
SQL lets them back into the table after the lock is released? Or will
they get an error?|||Yes that is right. If you have a lock on the table and someone attempts to
insert a new row (or delete, update etc) they will be blocked (halted state)
until one of the following occurs. Either their connection times out or the
lock gets released. The timeout is dependant on the connection settings as
to how long it waits.
Andrew J. Kelly SQL MVP
<unc27932@.yahoo.com> wrote in message
news:1123087028.818016.284860@.o13g2000cwo.googlegroups.com...
> OK - I think I have it. TABLOCK will allow others to read the table ,
> but not add any more records. Whereas TABLOCKX won't allow anyone else
> to even read the table, let alone, update/insert.
> And HOLDLOCK is used to hold the shared lock through the end of the
> transaction, not just the data operation/read/whatever.
> Is this right? Opinions?
> So what happens if a user attempts to insert a row at the moment I have
> it locked? Does their application just wait a few seconds, and then
> SQL lets them back into the table after the lock is released? Or will
> they get an error?
>

LOCK confusion

I'm reading Delaney's Inside SQL 2000 book & BOL right now and getting
more confused....my question deals with Locking. I have a simple one
column table with 20000 records or so. I want to in this order:
A) restrict/halt all insert/update activity on this table (the only
inserts that come to this table are from a trigger on another table)
B) move the contents over to another table
C) delete the contents
D) Open the table back up for the application to use
B & C are very basic & I'm fine with INSERT, DELETE, etc. I've never
used the locking functionality, however. What locking services does
SQL automatically take care of, and what will I have to specify in my
example?This is a multi-part message in MIME format.
--030108070508030207050909
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Well, with your fairly simple example you can solve the problem simply
by doing all the operations in an explicit transaction and specifying a
locking hint or two.
The HOLDLOCK locking hint will essentially put the session into
SERIALIZABLE isolation level, which means locks are held until the end
of the transaction. So if you also specify a TABLOCKX hint, then the
whole table will be locked with an exclusive lock, meaning no other SQL
process (SPID) will be able to acquire any kind of lock on any part of
that table until the transaction has been committed (or rolled back).
So the batch would be something like:
BEGIN TRAN
INSERT INTO MyArchiveTable (col1, col2, ...)
SELECT col1, col2, ... FROM MyTable *WITH (HOLDLOCK, TABLOCKX)*
DELETE MyTable
COMMIT TRAN
In fact, you probably wouldn't even need a TABLOCKX, a TABLOCK would
probably do because INSERTs & UPDATEs on the table will require an IX
lock on the table resource and an IX lock is incompatible with the S
lock on the table, so it will wait until the S lock has been released,
which will be when you commit the transaction.
(I hope this is right - my reference to all this (Inside SQL Server
2000) is sitting at home at the moment, so this is from memory.)
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
unc27932@.yahoo.com wrote:
>I'm reading Delaney's Inside SQL 2000 book & BOL right now and getting
>more confused....my question deals with Locking. I have a simple one
>column table with 20000 records or so. I want to in this order:
>A) restrict/halt all insert/update activity on this table (the only
>inserts that come to this table are from a trigger on another table)
>B) move the contents over to another table
>C) delete the contents
>D) Open the table back up for the application to use
>B & C are very basic & I'm fine with INSERT, DELETE, etc. I've never
>used the locking functionality, however. What locking services does
>SQL automatically take care of, and what will I have to specify in my
>example?
>
>
--030108070508030207050909
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>Well, with your fairly simple example you can solve the problem
simply by doing all the operations in an explicit transaction and
specifying a locking hint or two.<br>
<br>
The HOLDLOCK locking hint will essentially put the session into
SERIALIZABLE isolation level, which means locks are held until the end
of the transaction. So if you also specify a TABLOCKX hint, then the
whole table will be locked with an exclusive lock, meaning no other SQL
process (SPID) will be able to acquire any kind of lock on any part of
that table until the transaction has been committed (or rolled back).<br>
<br>
So the batch would be something like:<br>
<br>
BEGIN TRAN<br>
<br>
INSERT INTO MyArchiveTable (col1, col2, ...)<br>
SELECT col1, col2, ... FROM MyTable <b>WITH (HOLDLOCK, TABLOCKX)</b><br>
<br>
DELETE MyTable<br>
<br>
COMMIT TRAN<br>
<br>
In fact, you probably wouldn't even need a TABLOCKX, a TABLOCK would
probably do because INSERTs & UPDATEs on the table will require an
IX lock on the table resource and an IX lock is incompatible with the S
lock on the table, so it will wait until the S lock has been released,
which will be when you commit the transaction.<br>
<br>
(I hope this is right - my reference to all this (Inside SQL Server
2000) is sitting at home at the moment, so this is from memory.)<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"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:unc27932@.yahoo.com">unc27932@.yahoo.com</a> wrote:
<blockquote
cite="mid1123014090.456062.301480@.f14g2000cwb.googlegroups.com"
type="cite">
<pre wrap="">I'm reading Delaney's Inside SQL 2000 book & BOL right now and getting
more confused....my question deals with Locking. I have a simple one
column table with 20000 records or so. I want to in this order:
A) restrict/halt all insert/update activity on this table (the only
inserts that come to this table are from a trigger on another table)
B) move the contents over to another table
C) delete the contents
D) Open the table back up for the application to use
B & C are very basic & I'm fine with INSERT, DELETE, etc. I've never
used the locking functionality, however. What locking services does
SQL automatically take care of, and what will I have to specify in my
example?
</pre>
</blockquote>
</body>
</html>
--030108070508030207050909--|||I guess I'm not getting two things here...
A) the difference between TABLOCK and TABLOCKX. Does TABLOCK allow
reads on the table, and just not insert/updates? And TABLOCKX allow
nothing at all?
B) HOLDLOCK & its purpose. If I specify TABLOCK or TABLOCKX as a hint,
why is HOLDLOCK necessary?
Mike Hodgson wrote:
> Well, with your fairly simple example you can solve the problem simply
> by doing all the operations in an explicit transaction and specifying a
> locking hint or two.
> The HOLDLOCK locking hint will essentially put the session into
> SERIALIZABLE isolation level, which means locks are held until the end
> of the transaction. So if you also specify a TABLOCKX hint, then the
> whole table will be locked with an exclusive lock, meaning no other SQL
> process (SPID) will be able to acquire any kind of lock on any part of
> that table until the transaction has been committed (or rolled back).
> So the batch would be something like:
> BEGIN TRAN
> INSERT INTO MyArchiveTable (col1, col2, ...)
> SELECT col1, col2, ... FROM MyTable *WITH (HOLDLOCK, TABLOCKX)*
> DELETE MyTable
> COMMIT TRAN
> In fact, you probably wouldn't even need a TABLOCKX, a TABLOCK would
> probably do because INSERTs & UPDATEs on the table will require an IX
> lock on the table resource and an IX lock is incompatible with the S
> lock on the table, so it will wait until the S lock has been released,
> which will be when you commit the transaction.
> (I hope this is right - my reference to all this (Inside SQL Server
> 2000) is sitting at home at the moment, so this is from memory.)
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> unc27932@.yahoo.com wrote:
> >I'm reading Delaney's Inside SQL 2000 book & BOL right now and getting
> >more confused....my question deals with Locking. I have a simple one
> >column table with 20000 records or so. I want to in this order:
> >
> >A) restrict/halt all insert/update activity on this table (the only
> >inserts that come to this table are from a trigger on another table)
> >B) move the contents over to another table
> >C) delete the contents
> >D) Open the table back up for the application to use
> >
> >B & C are very basic & I'm fine with INSERT, DELETE, etc. I've never
> >used the locking functionality, however. What locking services does
> >SQL automatically take care of, and what will I have to specify in my
> >example?
> >
> >
> >|||OK - I think I have it. TABLOCK will allow others to read the table ,
but not add any more records. Whereas TABLOCKX won't allow anyone else
to even read the table, let alone, update/insert.
And HOLDLOCK is used to hold the shared lock through the end of the
transaction, not just the data operation/read/whatever.
Is this right? Opinions?
So what happens if a user attempts to insert a row at the moment I have
it locked? Does their application just wait a few seconds, and then
SQL lets them back into the table after the lock is released? Or will
they get an error?|||Yes that is right. If you have a lock on the table and someone attempts to
insert a new row (or delete, update etc) they will be blocked (halted state)
until one of the following occurs. Either their connection times out or the
lock gets released. The timeout is dependant on the connection settings as
to how long it waits.
--
Andrew J. Kelly SQL MVP
<unc27932@.yahoo.com> wrote in message
news:1123087028.818016.284860@.o13g2000cwo.googlegroups.com...
> OK - I think I have it. TABLOCK will allow others to read the table ,
> but not add any more records. Whereas TABLOCKX won't allow anyone else
> to even read the table, let alone, update/insert.
> And HOLDLOCK is used to hold the shared lock through the end of the
> transaction, not just the data operation/read/whatever.
> Is this right? Opinions?
> So what happens if a user attempts to insert a row at the moment I have
> it locked? Does their application just wait a few seconds, and then
> SQL lets them back into the table after the lock is released? Or will
> they get an error?
>sql

Local Report Confusion

Okay, so I'm working with a local report via the Web ReportViewer
control, and there is something going on that I don't quite get. I
created my object datasource and it was available to me via the "data
source" window when i created my RDLC report. All good here. Well along
the way, I have made changes to the class that represents my data
source object, and here is what I'm seeing being auto-inserted into my
ASPX file:
<rsweb:ReportViewer ID="ReportViewer1" runat="server" >
<LocalReport ReportPath="MyReport.rdlc">
<DataSources>
<rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
Name="MyObject" />
<rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
Name="MyObject1" />
<rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
Name="MyObject2" />
<rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
Name="MyObject3" />
<rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
Name="MyObject4" />
</DataSources>
</LocalReport>
</rsweb:ReportViewer>
<asp:ObjectDataSource ID="ObjectDataSource2" runat="server"
SelectMethod="GetMyObjects" TypeName="Merchant"></asp:ObjectDataSource>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
SelectMethod="GetMyObjects" TypeName="Merchant"></asp:ObjectDataSource>
By the time the "MyObject3" I started wondering if there is some hidden
(or at least not obvious) versioning going on with my object data
class, in that each time I change it and rebuild it, it is given a new
name in the data source pane. What's really odd though, is that only
the most recent appears to be available (assuming that is what is going
on). So why do all these new <rsweb:ReportDataSource /> rows keep
appearing in my ASPX? Am I somehow creating multiple connections to my
datasource? Is this a result from each time I change the class that
represents my underlying data source object? Do I need to keep all
these entries, or can I dump all the old ones and just keep the newest
one? Finally, how did I end up with multiple "OjectDataSource" objects?
I started deleting them, and then having to repoint the reportviewer
control to an existing ObjectDataSource control.
If anyone can clarify what is going on, I would be very grateful.
Thanks!
ChrisQuick update: I see that changing my class isn't what causes the
insertion of new <rsweb:ReportDataSource /> items in my ASPX. Just now
I added a new table, and now when I run it I get the error "A data
source instance has not been supplied for the data source 'MyObject5'."
If you look at my previous post, there was no "MyObject5". Now when I
click on the smart tag for the reportviewer object in design view and
select "Choose Data Sources" I get the pop up grid that now asks me to
associate an ObjectDataSource with this new MyObject5. So again, in
this dialog, I have a column called "Report Data Source" and a column
called "Data Source Instance". Right now my grid (after associating
this latest one) looks something like this:
Report Data Source Data Source Instance
MyObject ObjectDataSource1
MyObject1 ObjectDataSource1
MyObject2 ObjectDataSource1
MyObject3 ObjectDataSource1
MyObject4 ObjectDataSource1
MyObject5 ObjectDataSource1
Thanks!