Force read lock in a table, to avoid problems between parallel processes.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Force read lock in a table, to avoid problems...

11 posts, Page 1 of 2

1 2 »»

Force read lock in a table, to avoid problems between parallel processes.

Rate Topic

Display Mode

Topic Options

Author

Message

zeycus

Posted Tuesday, December 04, 2012 4:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 07, 2012 10:54 AM
Points: 4, Visits: 9

I am quite new to this, I'll try to explain my task and how I thought to solve it.

I have a Python process that needs to access a table, which key is an integer. When I want to
insert a new record, first I find the highest key with a

SELECT max(id) FROM TASKS

After that, given I captured that result in a variable id, I execute a

INSERT INTO TASKS
VALUES (id+1, ..., ..., ...).

It is working fine. What troubles me is that there will be different instances of this process running,
and I thought it is possible that two of them launch the first query, get the same max id, and then they will try to perform two insertions with the same primary key, which will obviously fail.

I thought the problem would be solved if, just before starting, the process would block the table
TASKS to everyone else, not allowing them to read. And the it would be restored after the insertion has been performed. How can I do this?

Or should I follow an entirely different approach?

Thanks in advance,

Post #1392363

bleroy

Posted Tuesday, December 04, 2012 6:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:59 PM
Points: 137, Visits: 546

The appropriate locking should take place by default - you can provide hints if you wish to lock objects at different levels (row, page, table), and you can force queries to read uncommitted changes etc.
In your case, you are correct that another process may grab the same ID, so I would encapsulate the finding of the max(id) FROM TASKS and the INSERT statement into a transaction.
You may want to think about having your key with the auto-increment property set to true? then you won't even have to worry about it.
Also, you may want to consider adding a TRY ... CATCH to your proc in case the ID is already taken, your proc could try with a new incremented ID.

B

Post #1392439

GilaMonster

Posted Tuesday, December 04, 2012 6:58 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:29 PM
Points: 37,731, Visits: 29,997

Try doing it in a single statement

Insert into Tasks ...
SELECT max(Id) + 1, ... From Tasks

That should work.

Otherwise you will need to force a different lock on the select. It's not a read lock that you want to force, SQL already takes one of those by default. You need a more restrictive lock to prevent multiple sessions running the max at the same time.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1392457

zeycus

Posted Tuesday, December 04, 2012 8:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 07, 2012 10:54 AM
Points: 4, Visits: 9

Thank you both for your quick replies.

bleroy (12/4/2012)

In your case, you are correct that another process may grab the same ID, so I would encapsulate the finding of the max(id) FROM TASKS and the INSERT statement into a transaction.B

Maybe this question is dumb. If I do this, having both queries are encapsulated in a transaction, and two different processes launch this transaction, the problem cannot happen? Is it sure that one of the transactions is executed completely before the other can start?

I also thought of what GilaMonster suggested, (controling the case when the table is empty, and the result of max is null). But I decided to ask for a more general solution.

Post #1392527

GilaMonster

Posted Tuesday, December 04, 2012 8:31 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:29 PM
Points: 37,731, Visits: 29,997

zeycus (12/4/2012)

No, not at all. If you just encapsulate that in a transaction it will make absolutely no difference whatsoever to the behaviour (assuming default isolation level). Two connections will be able to start the transaction, compute the same max, try to insert and one will fail.

You need to either do it in one operation as I suggested or you need more restrictive locks (shared locks are just that. Shared)

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1392538

zeycus

Posted Tuesday, December 04, 2012 8:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 07, 2012 10:54 AM
Points: 4, Visits: 9

GilaMonster (12/4/2012)

zeycus (12/4/2012)

I understand, thanks a lot. I will follow your suggestion to encapsulate it in a single query. If in the future I have the same problem with a more complex task, I will deal with it then Smile

Post #1392546

bleroy

Posted Tuesday, December 04, 2012 8:36 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:59 PM
Points: 137, Visits: 546

.. thanks for the correction GilaMonster

Post #1392547

Chris Harshman

Posted Tuesday, December 04, 2012 2:03 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 12:03 PM
Points: 1,564, Visits: 1,718

zeycus (12/4/2012)

...first I find the highest key with a
SELECT max(id) FROM TASKS
After that, given I captured that result in a variable id, I execute a
INSERT INTO TASKS
VALUES (id+1, ..., ..., ...).
...

Is there a reason you're not using IDENTITY for that id column? If you create the table like this:

CREATE TABLE TASKS
    (id INT IDENTITY(1,1) NOT NULL,
...

then SQL Server will handle ensuring id will be different if 2 sessions try to insert a new record at the same time, instead of trying to re-invent the wheel yourself.

Post #1392694

zeycus

Posted Wednesday, December 05, 2012 2:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 07, 2012 10:54 AM
Points: 4, Visits: 9

Chris Harshman (12/4/2012)

zeycus (12/4/2012)

...first I find the highest key with a
SELECT max(id) FROM TASKS
After that, given I captured that result in a variable id, I execute a
INSERT INTO TASKS
VALUES (id+1, ..., ..., ...).
...

Is there a reason you're not using IDENTITY for that id column? If you create the table like this:

CREATE TABLE TASKS
    (id INT IDENTITY(1,1) NOT NULL,
...

then SQL Server will handle ensuring id will be different if 2 sessions try to insert a new record at the same time, instead of trying to re-invent the wheel yourself.

Thank you, of course you are right. Someone suggested that yesterday and I changed it. But anyway I wanted to know how to do this in a general case.

Post #1392856

Bhuvnesh

Posted Wednesday, December 05, 2012 5:07 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

Chris Harshman (12/4/2012)

Is there a reason you're not using IDENTITY for that id column?

then there is no guarantee that destination table will have same ID for same records as it has in source table (remember...sequence of insert query will decide the id in case of identity colum in destination table)

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1392925

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions