Reader/Writer Question

Reader/Writer Question

Post by Ken Salte » Sat, 20 Jun 1998 04:00:00



I need help understanding how SQL server locking works.  Here is the
scenario:

I want to use a table to pass messages between processes (please don't ask
why)

The table has a msg field char(10), a field indicating which writer put the
msg in (int), and a field indicating that a new msg has appeared (bit).

The writer puts a msg into the table and sets the bit field to 1.  There are
multiple readers which are constantly looking at this table for any records
to appear with the bit field set to 1.  The first reader who sees the record
needs to hold the record (so other readers don't process the message),
process the msg, then set the bit field back to 0 so the server can reuse
it.

Does anyone have any suggestions as to how this can be done in SQL Server?
It's easy in FoxPro, just lock the record and have the readers test for to
see if the record is locked.

TIA,
Ken Salter

 
 
 

Reader/Writer Question

Post by Gianluca Hot » Tue, 23 Jun 1998 04:00:00




Quote:> I need help understanding how SQL server locking works.  Here is the
> scenario:

> I want to use a table to pass messages between processes (please don't
ask
> why)

> The table has a msg field char(10), a field indicating which writer put
the
> msg in (int), and a field indicating that a new msg has appeared (bit).

> The writer puts a msg into the table and sets the bit field to 1.  There
are
> multiple readers which are constantly looking at this table for any
records
> to appear with the bit field set to 1.  The first reader who sees the
record
> needs to hold the record (so other readers don't process the message),
> process the msg, then set the bit field back to 0 so the server can reuse
> it.

Check out HOLDLOCK to hold locks for your SELECT
statements or SET TRANSACTION ISOLATION LEVEL
to serialize transactions.

Best regards,
--
Gianluca Hotz                                __
Technical Service Manager at Alphasys srl   /  \/ /
MCP Windows & MCP + MVP SQL Server         / /\/ /
Currently working for Ekar spa             \ \/ /\
http://ghotz.home.ml.org                    \__/\ \

 
 
 

Reader/Writer Question

Post by Ken Salte » Tue, 23 Jun 1998 04:00:00


Does HOLDLOCK lock the page?  So that if I have more than one record in my
table, I will possibly lock more than one record when I only want to lock
one record?




>> I need help understanding how SQL server locking works.  Here is the
>> scenario:

>> I want to use a table to pass messages between processes (please don't
>ask
>> why)

>> The table has a msg field char(10), a field indicating which writer put
>the
>> msg in (int), and a field indicating that a new msg has appeared (bit).

>> The writer puts a msg into the table and sets the bit field to 1.  There
>are
>> multiple readers which are constantly looking at this table for any
>records
>> to appear with the bit field set to 1.  The first reader who sees the
>record
>> needs to hold the record (so other readers don't process the message),
>> process the msg, then set the bit field back to 0 so the server can reuse
>> it.

>Check out HOLDLOCK to hold locks for your SELECT
>statements or SET TRANSACTION ISOLATION LEVEL
>to serialize transactions.

>Best regards,
>--
>Gianluca Hotz                                __
>Technical Service Manager at Alphasys srl   /  \/ /
>MCP Windows & MCP + MVP SQL Server         / /\/ /
>Currently working for Ekar spa             \ \/ /\
>http://ghotz.home.ml.org                    \__/\ \

 
 
 

Reader/Writer Question

Post by Gianluca Hot » Wed, 24 Jun 1998 04:00:00




Quote:> Does HOLDLOCK lock the page?  So that if I have more than one record in
my
> table, I will possibly lock more than one record when I only want to lock
> one record?

Yes it locks the page. One way is to add a pad field so that only
one record reside on a page. Which is not an elegant solution,
it uses more space than needed and it'll break with SQL Server 7
8k pages.

Best regards,
--
Gianluca Hotz                                __
Technical Service Manager at Alphasys srl   /  \/ /
MCP Windows & MCP + MVP SQL Server         / /\/ /
Currently working for Ekar spa             \ \/ /\
http://ghotz.home.ml.org                    \__/\ \

 
 
 

Reader/Writer Question

Post by Ken Salte » Sat, 27 Jun 1998 04:00:00


Okay, is there any way on earth to "lock" one record in a SQL Server table?

Urgently awaiting,
Ken




>> Does HOLDLOCK lock the page?  So that if I have more than one record in
>my
>> table, I will possibly lock more than one record when I only want to lock
>> one record?

>Yes it locks the page. One way is to add a pad field so that only
>one record reside on a page. Which is not an elegant solution,
>it uses more space than needed and it'll break with SQL Server 7
>8k pages.

>Best regards,
>--
>Gianluca Hotz                                __
>Technical Service Manager at Alphasys srl   /  \/ /
>MCP Windows & MCP + MVP SQL Server         / /\/ /
>Currently working for Ekar spa             \ \/ /\
>http://ghotz.home.ml.org                    \__/\ \

 
 
 

Reader/Writer Question

Post by Gianluca Hot » Tue, 30 Jun 1998 04:00:00




Quote:> Okay, is there any way on earth to "lock" one record in a SQL Server
table?

> Urgently awaiting,
> Ken

SQL Server 6.5 supports record level locking *only* for
insert operations. A select operation will always result in
a page level lock. So the answer is no, you can't lock a
single records in a neat way if you are reading the table.

If you really must lock a single record then you can re-design
your table to ensure that each record occupies only one page.
The way to achieve this is to use fake fields to pad the record.
However this is an extremely kludgy and inefficient solution
that will break with SQL Server 7.0.

Best regards,
--
Gianluca Hotz                                __
Technical Service Manager at Alphasys srl   /  \/ /
MCP Windows & MCP + MVP SQL Server         / /\/ /
Currently working for Ekar spa             \ \/ /\
http://ghotz.home.ml.org                    \__/\ \

 
 
 

1. Looking for info on reader/writer scenario

I'm having some trouble setting up a news database.  I have between 10
and 20 users concurrently reading from the table of news stories.  In
the meanwhile, I need to insert new stories.

Can someone refer me to documents/books that offer solutions and
optimization hints for this scenario?  I'm using MS SQL Server 6.5, if
it makes any difference.

Thanks in advance,

Boris Burtin
The Daily Rocket Company

2. Returning Data from a stored procedure

3. Writers block readers

4. US-OR Oracle DBA

5. Concurrency/reader-writer problem

6. Vendor Library Error: Transaction not available

7. VB Acrobat Writer / Reader and signature

8. Help with an update statement

9. Native Java .DBF reader/writer

10. clipper MEM reader and writer

11. Writer not stop Reader ?

12. Concurrency/reader-writer problem