Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 22nd, 2003, 11:00 PM
Authorized User
 
Join Date: Sep 2003
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to devrishi
Default Multiuser Considerations In SQL

Hi there!!

I've recently switched over to vb/sql development and finding
it difficult to write application in multiuser environment.

Previously i was working on Foxpro 2.6 (dos) where i had the
facility of lock()/flock() function to lock particular record or
file.

Can anybody help me ?
Regards
Devrishi

 
Old September 23rd, 2003, 09:59 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Study up on database Transactions. They are far superior to any manual locking and unlocking of tables or rows, since the system manages concurrency, not you. See SQL Server BOL entries for "transactions" for some pretty good introductory information.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old September 27th, 2003, 10:48 PM
Authorized User
 
Join Date: Sep 2003
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to devrishi
Default

hi Jeff
thanx for your reply

as u advised , i browsed through sql bol (Transaction,locking etc)
But still i am not clear when to use which isolation level and locking hints.

right now i am worried about only two conditions...

1) Automatic generation of Custom ID (for various documents) under
multiuser enviroment.

2) what if a single record is accessed by two user at the same time.

can u suggest me some good book by Wrox Press or any other press
which deals with such issues...

Thanx in advance.

Regards
Devrishi





 
Old September 28th, 2003, 07:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:
... i am not clear when to use which isolation level and locking hints.
The four different transaction isolation levels offer increasing concurrency in multiuser environments at the cost of potential integrity issues primarily centering around the repeatability of data access within a transaction. More restrictive levels hold more restrictive locks for longer periods, so while data integrity is better maintained, it is at the cost of decreased concurrency.

For example, with the default level, READ COMMITTED, you can read a set of rows (as defined by some WHERE clause) then read the same set again within your transaction and find that rows have been added or deleted or updated in the interim by other processes. You won't see rows that are in the process of being changed by some other process's transactions (you would if you set your level to READ UNCOMMITTED), but you will see their results when their transaction is committed.

The next level "up", REPEATABLE READ, prevents you from seeing any other updates, but still allows the possibility of seeing rows which have been inserted or deleted by other processes. The repeatability of reads is at a cost, though, because the way this is done is that exclusive locks are placed on the data, preventing other users (who also attempt REPEATABLE READS) from even reading the data.

The highest level, SERIALIZEABLE, means that even inserts and deletes are prevented from occurring. While this offers the highest level of data integrity, it does so at the cost of the lowest level of concurrency. This is because the exclusive locks are set on a range of rows (possibly the entire table), so concurrency is reduced basically to the point of single threading access to the table(s) in question.

The question of which level to use when is not a simple one to answer, since it all depends on the requirements of your application. For browsing-type functions you may be quite happy with the lowest level, unless, of course, major business decisions are made based on the data presented. If you are offering credit terms based on a current balance, you might not want to have your credit officer base that decision on data which doesn't necessarily reflect the most recent purchase by the customer. Marketing analysis, on the other hand, may be quite reasonable working with data which is not quite the most up-to-date. Making sure each operation is at the appropriate level insures that they don't interfere with one another inappropriately. This can require some thought.

IMO, you should be careful with locking hints. I tend to use these only in response to specific concurrency/integrity issues which arise. It's too easy to actually make things worse by the inappropriate use of hints in the wrong place.
Quote:
quote:
1) Automatic generation of Custom ID (for various documents) under
multiuser enviroment.
Here, you certainly don't want two documents to be assigned the same ID (I'm assuming you are not talking about identity columns as the ID), so SERIALIZEABLE is probably the appropriate level to use. Because this is the most restrictive level, you'll want to be certain the the duration of this transaction is as short as possible. Set the transaction level, begin the transaction, assign the ID and commit as fast as you can. Other users will simply wait on the lock, so to insure reasonable concurrency you want to hold the lock for as short a period as possible.
Quote:
quote:
2) what if a single record is accessed by two user at the same time.
Depends on what you want; see above.
Quote:
quote:
can u suggest me some good book by Wrox Press or any other press
which deals with such issues...
Best book I've found on this is Inside SQL Server 2000 by Kalen Delaney, published by Microsoft Press.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old September 28th, 2003, 01:53 PM
Authorized User
 
Join Date: Sep 2003
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to devrishi
Default

hi jeff!!

thanx a lot..for your time...
i will try to arrange this book Inside SQL Server 2000.
this book is available in india...(good news)

i will trouble you again..once i go through this book..

thanx once again.

warm regards
Devrishi:)

 
Old October 8th, 2003, 11:58 PM
Authorized User
 
Join Date: Sep 2003
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to devrishi
Default

Hi Jeff!

i want to discuss following case with you.
i am writing an invoice entry module in which invoice no. will be system generated (custom). We are keeping a Pointer Table for storing our document numbers. Pointer table contains only two
fields DocType and DocNo.

i am plainning to do it in the following way..

    Conn.IsolationLevel = adXactIsolated
    Conn.BeginTrans
    strsql = "select docno from pointer where doctype='SRIR'" '
    // SRIR is document code

    Rs.Open strsql, Conn, adOpenKeyset, adLockOptimistic
    NewInvoice = rs!docno ' Read latest no.
    Rs!docno = NewInvoice + 1 ' store next no.

    *--- insert records in invoice master and invoice details.
    insert into invoice ......

    Conn.CommitTransaction


i have kept codes to increment running document no. and
to save invoice information in the same transaction becuase
in case of any error all the changes can be rolled back.

Also i want to know that how locktype and cursortype are linked with
isolation level ?

Regards
Devrishi

 
Old October 9th, 2003, 07:46 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

I avoid using recordsets for such things as you are doing, preferring to use a stored procedure instead. Stored procedures are faster and use much fewer resources on both the client and the server. The only situations I use recordsets in is display situations and occasionally disconnected recordsets for disconnected processes doing bulk (or at least multiple) updates. Disconnected recordsets are client-side recordsets so they introduce a different set of problems pertaining to update collisions. The display recordsets are read-only, forward-only recordsets (so-called "firehose" cursors) to load combo boxes, data grids, etc. These types of recordsets are reasonably lightweight.

The stored procedure might be along the lines of:
Code:
CREATE PROCEDURE CreateDocument
    @InvoiceNumber integer OUTPUT        --in case the client needs it
    @DocType char(4),
    <other invoice data as input params>
as
    SELECT @InvoiceNumber=DocNo+1 FROM pointer WHERE DocType=@Doctype;
    UPDATE Pointer SET DocNo=@InvoiceNumber WHERE DocType=@Doctype;
    INSERT Invoice (InvoiceNumber, ...) VALUES (@InvoiceNumber, ...)
Then the stored procedure is executed via a command object bounded by a transaction. I think a REPEATABLE READ transaction isolation level is appropriate in this situation. Rows aren't going to be inserted into the 'pointer' table so it would be best to only lock the row that will be updated as indicated by the DocType, and allow other rows (DocTypes) to be updated simultaneously, thus at least allowing concurrency for the insertion of different types. This level also keeps the Invoice table from being locked so the other inserts can proceed.

Cursor type is somewhat related to the transaction isolation level insofar as the cursor type determines the repeatability and/or visibility of data rows, and that is also controlled by the transaction level. You should make sure they are consistent. Don't set the isolation level to serializeable if you are using a firehose cursor, for example - that's just a waste of resources...

The lock type you are referring to I think is the ADO lock type and is used to control the locks managed by the OLEDB provider in the database on the data rows in the recordset.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old October 9th, 2003, 10:12 PM
Authorized User
 
Join Date: Sep 2003
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to devrishi
Default

hi jeff!!
thanx a lot for your time once again!!

But jeff as u suggested to use stored procedure for creating
documents like invoices,purchase orders etc.

i have two questions here..

Question 1.
when you say
"Then the stored procedure is executed via a command object bounded by a transaction"

Are u suggesting me to set isolation level in stored procedure or using ADO connection object. does it make any difference if i do it through ADO connection object.

Question 2.
How can i pass other information like Dealer Code,
items detail etc. becuase one invoice may contain 100 items.
should i use text string or xml ? or is there any other way out ?

Regards
Devrishi




 
Old October 10th, 2003, 07:03 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Are u suggesting me to set isolation level in stored procedure or using ADO connection object. does it make any difference if i do it through ADO connection object.
I don't think it makes any difference at all. I tend to set the isolation level in the stored procedure because it's easier to maintain and modify there. I also tend to execute the transaction begin/commit/rollback in the client code because it's easier to control things from there, IMO. But the actual situation may dictate what you do.
Quote:
quote:
How can i pass other information like Dealer Code,
items detail etc. becuase one invoice may contain 100 items.
should i use text string or xml ? or is there any other way out ?
I would use one stored procedure to obtain the invoice (document) number and insert the invoice "header" data, then another which takes the invoice number and other data for one detail line and inserts detail lines into each table, and loop through the detail lines calling the second repeatedly.

Wrap the whole thing in a transaction, set the isolation level at REPEATABLE READ for the first procedure and since you are inserting new data for the new detail lines probably READ COMMITTED for the second procedure, and commit when you've inserted all the detail lines...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old October 10th, 2003, 12:23 PM
Ned Ned is offline
Authorized User
 
Join Date: Jun 2003
Posts: 66
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Devrishi,
You should consider COM+ for writing multiuser applications. The beauty of this technology is that you write a program for single user then COM+ take care of all the locking and unlocking records. I also advise you to avoid data bound. That's exactly what we are doing in our enterprise application development.



-Ned





Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiuser Access application. rupen Access VBA 2 August 10th, 2007 06:46 AM
COM with VB6 (Majour Considerations) haianoop VB Components 0 December 28th, 2005 04:06 PM
design considerations iqbal.arshad General .NET 0 August 22nd, 2005 02:28 AM
VB .Net MultiUser Environmnet? SolverSurfer VS.NET 2002/2003 1 June 30th, 2005 05:39 AM
Table Size Considerations hugh@kmcnetwork.com SQL Server 2000 0 April 24th, 2005 08:30 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.