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
No comments:
Post a Comment