how to serialize concurrent thread connections? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to serialize concurrent thread connections?

Author

Topic

BSLillo
Starting Member

9 Posts

Posted - 2005-12-13 : 04:19:37

Hello everyone!
I'm new and i have a problem...i would like to serialize some (about 50 or more) threads which update a record in my SQL Table.
I'm using MS SQL Server, but i'm not able doing this...in what way could i operate?
Do i have to write something like :

BEGIN TRANSACTION
...
select..
....
update...
...
COMMIT TRANSACTION

I would like that when a thread enters in a transaction in order to make an update, noone should read the value not yet affected by the update...in other words i would like to serialize concurrent threads connections in a table...
Please help me!!!
Thank you very much in advance!!!

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 04:50:45

Duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59018

CLages
Posting Yak Master

116 Posts

Posted - 2005-12-13 : 08:21:09

from BOL

Clages

Embedded SQL for C and SQL Server

SET CONCURRENCY
The SET CONCURRENCY statement sets the concurrency option for standard cursors.

Syntax
SET CONCURRENCY {LOCKCC | OPTCC | OPTCCVAL | READONLY}

Arguments
LOCKCC (default if SET ANSI_DEFAULTS is ON)

Specifies intent to update locking. If a FETCH statement is issued within a user-defined transaction, an exclusive lock is placed on the data before it is fetched. The exclusive lock prevents others from viewing or changing the data until the lock is released when the transaction closes.

OPTCC (default if SET ANSI_DEFAULTS is not ON)

Specifies optimistic concurrency control based on a timestamp column (if available) or all nontext, nonimage columns.

OPTCCVAL

Specifies optimistic concurrency control based on all nontext, nonimage columns.

READONLY

Specifies read-only cursors. Data retrieved by a FETCH statement cannot be modified.

Remarks
After the SET CONCURRENCY statement is issued, it affects all subsequent OPEN statements. Using the DECLARE CURSOR FOR UPDATE statement has the same effect as SET CONCURRENCY LOCKCC, and any reference to the SET CONCURRENCY statement is ignored. The SET CONCURRENCY statement is also ignored if you are using browse cursors.

If the LOCKCC option is used, you can choose to hold open the user-defined transaction only around each fetch. This requires that a SET FETCHBUFFER statement be issued before opening the cursor. Or you can choose to hold open the user-defined transaction for the life of the cursor. Note that holding open a transaction during LOCKCC cursor operations can significantly reduce concurrency and degrade performance.

If the OPTCC or OPTCCVAL option is used, an UPDATE WHERE CURRENT OF statement can fail if the row has been changed since the last FETCH statement. The application must be able to handle this situation.

Examples
EXEC SQL SET CONCURRENCY READONLY;

See Also

DECLARE CURSOR

SET FETCHBUFFER

SET ANSI_DEFAULTS

SET SCROLLOPTION

SET CURSORTYPE

Standard DB-Library Cursors

©1988-2000 Microsoft Corporation. All Rights Reserved.

BSLillo
Starting Member

9 Posts

Posted - 2005-12-13 : 09:15:08

YEH, THANX...BUT WHAT IS THE CORRECT SYNTAX?
I WROTE:

" 'SET CONCURRENCY LOCKCC BEGIN TRANSACTION T3 SELECT * FROM prova COMMIT TRANSACTION T3' "

AND THEN

"'SET CONCURRENCY LOCKCC BEGIN TRANSACTION T4 UPDATE prova SET a1 = ',v_value,' COMMIT TRANSACTION T4' "

BUT NO CONCURRENCY! WHY? THE TWO THREADS STILL WORK WITHOUT CONCURRENCY!

AEXCUSE ME BUT I'M NEW AND I HAVE NO PRACTICE!
tHANK YOU!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources