June 2, 2011 at 1:25 pm
I have an update statement to run which will update multiple records in a busy table.
There is very likely that some of the records I will attempt to update will be in use and locked.
I'd like to update all available records and, if possible, get a list of locked records.
I was hoping the table hint READPAST would help with this but it appears to be used with select statements only.
I've tried joining tables within an update thinking this might work but nothing.
I've tried using a cursor with some success but I'd rather stay away from cursors completely.
I've been at this for ages and I'm not getting anywhere. Am I missing something, surely this is possible?
Easy to recreate in query analyser
In Query Analyser Window 1
create table t
(a int,
b int)
insert into t values (1,2)
insert into t values (3,4)
insert into t values (5,6)
insert into t values (7,8)
begin tran
update t set b = 100 where a = 1
--record now locked
--rollback tran
In Query Analyser Window 2
update t set b = 200
--run this and the query justs sits there, waiting for the lock to be released
--would really like it to update the available records and skip the locked record
--when rollback in run the 1st query window this is free to run.
Any ideas very welcome...
June 2, 2011 at 1:50 pm
Well, you can look into Isolation Levels and all that for data on locks and how to deal with them.
But really, since transactions are supposed to be Atomic (ACID), you really can't "update the ones that don't have locks on them". It's something SQL Server is designed to prevent. You can either commit a whole update, or rollback a whole update, not partial commit+partial rollback.
If you really need a feature like that, SQL Server isn't the way to go. You'll need to look into one of the NoSQL solutions, or build something custom out of open-source database engine code.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 2, 2011 at 2:06 pm
I see what you mean about Atomic transations and agree.
I suppose this update statement isn't an 'all or nothing' situation but if I'm to break it down into single transactions I guess I'm looking at creating a cursor, a lock timeout and some error trapping.
But a cursor? Isn't there another way.
June 2, 2011 at 5:14 pm
Stephen Ward (6/2/2011)
I see what you mean about Atomic transations and agree.I suppose this update statement isn't an 'all or nothing' situation but if I'm to break it down into single transactions I guess I'm looking at creating a cursor, a lock timeout and some error trapping.
But a cursor? Isn't there another way.
Atomic to one side, I understand the need and here is the way to do what you want....sometimes it is better to update 7/10 than none and catch the other 3/10 later.
UPDATE AT
SET
b = 200
OUTPUT
INSERTED.*
FROM
t AS AT WITH (READPAST)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 3, 2011 at 6:39 am
mister.magoo (6/2/2011)
Stephen Ward (6/2/2011)
I see what you mean about Atomic transations and agree.I suppose this update statement isn't an 'all or nothing' situation but if I'm to break it down into single transactions I guess I'm looking at creating a cursor, a lock timeout and some error trapping.
But a cursor? Isn't there another way.
Atomic to one side, I understand the need and here is the way to do what you want....sometimes it is better to update 7/10 than none and catch the other 3/10 later.
UPDATE AT
SET
b = 200
OUTPUT
INSERTED.*
FROM
t AS AT WITH (READPAST)
Doesn't work.
I just tested this:
Connection one:
CREATE TABLE dbo.LockTest (
ID TINYINT IDENTITY PRIMARY KEY,
Col1 CHAR(1));
GO
INSERT INTO dbo.LockTest(Col1)
VALUES ('a'),('b');
GO
BEGIN TRANSACTION;
UPDATE LT
SET Col1 = 'c'
FROM dbo.LockTest AS LT WITH (READPAST)
WHERE ID = 1;
SELECT @@TRANCOUNT;
Transaction on ID 1 held open.
Connection 2:
BEGIN TRANSACTION;
UPDATE LT
SET Col1 = 'c'
FROM dbo.LockTest AS LT WITH (READPAST)
WHERE ID IN (1, 2);
COMMIT;
SELECT *
FROM dbo.LockTest;
Because the transaction from connection 1 is uncommitted (as with any normal concurrency issue), connection two is blocked from committing till connection 1 either commits or rolls back.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2011 at 6:43 am
GSquared (6/3/2011)
Doesn't work.
Thanks for the feedback.
It worked fine on mine, but I was not specifying certain IDs.
I will try your version and see if that is the reason.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 3, 2011 at 6:53 am
It seems that (for me at least) it works as long as you remember that your final
SELECT *
FROM dbo.LockTest
is the part that is being blocked...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 3, 2011 at 9:53 am
mister.magoo (6/3/2011)
@GSquared:It seems that (for me at least) it works as long as you remember that your final
SELECT *
FROM dbo.LockTest
is the part that is being blocked...
Works for me too. It's easier to see this way (on SQL 2008 R2 Standard Edition).
Query Window 1:
CREATE TABLE dbo.LockTest
(
ID TINYINT IDENTITY
PRIMARY KEY,
Col1 CHAR(1)
) ;
GO
INSERT INTO dbo.LockTest
(Col1)
VALUES ('a'),
('b') ;
GO
BEGIN TRANSACTION ;
UPDATE LT
SET Col1 = 'c'
FROM dbo.LockTest AS LT WITH (READPAST)
WHERE ID = 1 ;
SELECT @@TRANCOUNT ;
Query Window 2:
SET LOCK_TIMEOUT -1 -- indefinite
BEGIN TRANSACTION ;
UPDATE LT
SET Col1 = 'c'
FROM dbo.LockTest AS LT WITH (READPAST)
WHERE ID IN (1, 2) ;
SELECT @@ROWCOUNT AS rows_updated
COMMIT ;
SELECT *
FROM dbo.LockTest
WHERE ID = 2 ;
GO
SET LOCK_TIMEOUT 500 -- 1/2 second so we'll quickly see the error
SELECT *
FROM dbo.LockTest
WHERE ID = 1 ;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 3, 2011 at 10:17 am
PS I think it's odd that you can get away with this in SQL Server. I had a similar initial thought to what GSquared posted...a visceral "NO" to the premise of the question, followed by thoughts of ACID. I then settled into the idea and realized that SQL Server logs row updates so why not support updating only rows that are not locked by another process? The transaction would still be ACID compliant wouldn't it? Maybe the discussion deserves a new thread in the Relational Theory forum :hehe:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply