[RESOLVED] Client Server Concept

[jonathan_giuliano]

could someone tell me about lock types, cursor locations in ADO, the theory and the practices??

cuz I still write client server applications without using these things, not knowing how they work...

[Hack]

cursor types 4

dynamic
static
keyset
forward only

Lock types 3

pessimistic
optimistic
read only

Additional info.

http://support.sas.com/documentation...rlocktypes.htm

http://www.w3schools.com/ado/prop_rs_cursortype.asp

[jonathan_giuliano]

How are they applied on client-server applications???

[GaryMazzone]

That is a wide open question. The answer depends on what you want to accomplish in any portion of the application.

[Hack]

That is a wide open question. The answer depends on what you want to accomplish in any portion of the application.

And this is exactly why I couldn't, and still can't, be more specific with answer.

I don't know how you need to use them.

[szlamany]

They are just tools to achieve an end.

Which end you get to is up to you.

We don't mind having last-in get the update - so we don't care much about locks. Two users grab the same record - make a change - and save. Last-in gets the update.

Others consider that not good.

And there are ways to avoid that which involves locks and ways that avoid it with less cost.

[jonathan_giuliano]

That is a wide open question. The answer depends on what you want to accomplish in any portion of the application.

I want to make a multi-user, client-server (network) database application..
but i can't manage the data manipulations when a lot of users access the same data (tables). there's always errors

I know that these things (lock types, cursor) are the answer. but I don't know how to use them properly

[si_the_geek]

There are explanations of them (and basic advice on which to use) in the article What do the parameters of the recordset.Open method mean? from our Database Development FAQs/Tutorials (at the top of this forum)

In most cases, but not always, what you want is locks being active for as short a time as possible (so reduce the time between editing and the .Update as much as you can), and limiting the cursor to reduce the need for excessive data transfer across the network.

However, as implied/said by others above, these are certainly not the only things to consider - there are many other things (such as the overall design of your applications interaction with the database) which can have a bigger impact. To be able to give you apt advice, we need to know more about your particular situation (at the very least, what errors you are getting, and the database system you are using).

[jonathan_giuliano]

I'm using SQL Server 2000 and ADODB

the errors is like "zombie state" or something... (after calling connection.committrans)

and what is the cursor for?how does it work??

[szlamany]

Basically a cursor is referring to who will serve the rows and where they are located.

The rows reside in a recordset - as you can imagine that's a big block of memory n-bytes wide times x-number-of-rows.

If the recordset is large - lots of memory - you must consider where to place it. Putting it on each client is expense network wise.

I personally don't like to think about locks and cursors.

We always use forward-only/read-only cursors. Basically that means that the server is gathering the data and handing it off to my program as quickly as possible. We typically read all that data into controls in the VB program and then destroy the RECORDSET object - leaving no connection between our client and the database. Insert's and updates are pushed one-row at a time back at the database via action queries.

The amount of reading and research you can do about cursors and lock types is huge. You should start reading about it and come back with questions we might be able to answer - otherwise the topic is a bit wide for a single "do it this way" and you will like it answer.

[jonathan_giuliano]

I read the ADO Docs from VB Help (F1)
I got these

CURSOR TYPES

adOpenForwardOnly
Forward-only cursor. Default. Identical to a static cursor except that you can only scroll forward through records. This improves performance in situations when you need to make only a single pass through a recordset.

adOpenKeyset
Keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your recordset. Data changes by other users are still visible.

adOpenDynamic
Dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the recordset are allowed, except for bookmarks if the provider doesn't support them.

adOpenStatic
Static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.


What does VISIBLE mean???


CURSOR LOCATIONS

adUseNone
No cursor services are used. (This constant is obsolete and appears solely for the sake of backward compatibility.)

adUseClient Uses client-side cursors supplied by a local cursor library. Local cursor services often will allow many features that driver-supplied cursors may not, so using this setting may provide an advantage with respect to features that will be enabled. For backward compatibility, the synonym adUseClientBatch is also supported.

adUseServer Default. Uses data-provider or driver-supplied cursors. These cursors are sometimes very flexible and allow for additional sensitivity to changes others make to the data source. However, some features of the Microsoft Client Cursor Provider (such as disassociated recordsets) cannot be simulated with server-side cursors and these features will be unavailable with this setting.

I don't understand at all about this cursor thing. blank

LOCK TYPES

adLockReadOnly
Default. Read-only—you cannot alter the data.

adLockPessimistic
Pessimistic locking, record by record—the provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing.

adLockOptimistic
Optimistic locking, record by record—the provider uses optimistic locking, locking records only when you call the Update method.

adLockBatchOptimistic
Optimistic batch updates—required for batch update mode as opposed to immediate update mode

same.

???
Help Me...

[szlamany]

VISIBLE - means whether user A sees changes made by user B.

Two users can have access to a recordset of data at the same time and based on the type of cursor changes made by user A can be immediately seen by user B - without re-getting the whole recordset.

You could imagine that if the SERVER retains a hold of the recordset - that it's location (of the cursor) is of data on the SERVER - that managing this VISIBLE sharing might be less expensive. I'm not an expert of the differences between cursor types as I only use forward only/read only as I stated before.

Forget about locking for a bit.

Do you have any grasp at all of the cursor concept?

How about just the concept of a recordset?

Are you in school and trying to finish a project?

[dilettante]

To get a complete picture can be very elusive. Reading more material may only muddy the picture at first because each author may have a slightly different goal or requirement in mind. This results in information that seems to argue with itself, one article claiming "X is best" and another "not X is best."

A few more things to look at if you haven't seen them:

Understanding Cursors and Locks

Server Side Cursors and ADO Cursor Types

ADO Cursors

6 Ways to Boost ADO Application Performance

SAMPLE: Rowlock.exe Demonstrates Row-Level Locking Using ADO & SQL Server

INFO: Understanding ADO Transactions with MS SQL Server

[szlamany]

That first link didn't work for me - just in case you can edit it somehow...

From the second link

One of the unexpected downside of server-side cursors is temporary tables. The sp_cursor stored procedures typically use temporary tables to generate their results. In some cases I've seen one temporary table created and destroyed per record returned by the cursor. This generated significant contention in tempdb and was forcing the system to single-thread through object creation in tempdb. This occurred in SQL Server 7 and I've been unable to test in SQL Server 2000 under load.

In summary, to minimize round trips to SQL Server use client-side cursors or server-side, read-only, forward-only (aka "firehose") cursors. They can give you a significant performance boost.

You have to have a reason to use a specific cursor type/location. That reason has to be based on how you want your app to react to other user changes.

If the original poster could talk a bit about what they want to accomplish in their current project we can help give specific reasons for going in certain directions.

[jonathan_giuliano]

Do you have any grasp at all of the cursor concept?

How about just the concept of a recordset?

Are you in school and trying to finish a project?

ok I get the picture of the cursor
I'm a rookie, trying to optimize every app i write
i know a bit about recordset and stuff, but i don't understand the details...

[szlamany]

Are you concerned about what other users will see if they query a record that you have queried at the same time?

What kind of apps do you write? How many simultaneous users? I've got app's with several hundred teachers all posting attendance about the same population of students all day long.

The act of posting consists of the teacher clicking a cell in a flexgrid - first click = A, second click = T, third click = D, fourth click = back to space. Each click fires off an INSERT/UPDATE type call to a table for the student on that row. For the class and date that the teacher selected before calling up that screen. No Locks are required for this. A teacher could keep the same class of students selected all day long and we would not care one bit.

It's the act of "clicking" a spot that puts data into the DB.

This process was architected with these thoughts up front.

You have to tell us something about what you are doing with your apps...

[jonathan_giuliano]

ahh
ok
its a POS-like app with quite a lot of cashiers. when a cashier pocessess the sales order, it decrements the 'stock' field. but what if the other cashiers do the same thing at the same time when the stock is 1 left.

and what if they access the same tables on the database like querying the same items and sometimes updating its name at the same time...

another case is hotel app. when a user on a 'checkin pc' querying available rooms and displaying on a datagrid control and at the same time, at the 'checkout pc', a guest is checking out which will set the room's field property to 'available' or probably the guest just wants to move to another room which will change the availability of two rooms. in the meantime the checkin pc has finished querying the available rooms but the information of the availability is wrong...

and one more thing.what if two users put the same value into the primary key of a table at the same time??

[si_the_geek]

its a POS-like app with quite a lot of cashiers. when a cashier pocessess the sales order, it decrements the 'stock' field. but what if the other cashiers do the same thing at the same time when the stock is 1 left.

If you actually decrement the database field (using an Update statement, rather than reading via a recordset and writing back a value based on the value in the recordset), the stock level should be accurate.

Common sense says that if there is only 1 item in stock, only 1 can be taken to the cashiers to be sold.. so that situation should not be able to occur. However, it is worth thinking about adding an extra condition to the Update's Where clause, so that the level will not be reduced if it is already 0.

and what if they access the same tables on the database like querying the same items and sometimes updating its name at the same time...

This is where Locking comes in.. pick a locking method which acts as you want. The explanations of each option are in the help (as you posted above), and hopefully clearer in the article I linked to.

Of course, what you want may be the same as mentioned above:

We don't mind having last-in get the update - so we don't care much about locks. Two users grab the same record - make a change - and save. Last-in gets the update.

another case is hotel app. when a user on a 'checkin pc' querying available rooms and displaying on a datagrid control and at the same time ...

I have no idea how the DataGrid acts, but the recordset can automatically stay up to date - see CursorType in the help, or the article I linked to.

and one more thing.what if two users put the same value into the primary key of a table at the same time??

Assuming you mean in different rows of the same table, they can't.. the server will stop that from happening, as it takes all edit/add requests and acts on them one at a time.

One user (whichever the server determines was 'first') will actually write the values, the other(s) will get an error.

[jonathan_giuliano]

alright its getting clearer

[szlamany]

Picking a room is a real-time event. If you have a dozen pc's grabbing rooms to assign to people then you have to handle the situation where the room you are trying to grab was grabbed just a nano-second earlier by another PC.

That doesn't require a lock - it's a simple trick in the WHERE clause

Update Room Set Available='N' Where Room='123' and Available='Y'

Notice that room 123 is being updated to 'N' in the Available field. But only where the Available field is still equal to 'Y'. The SQL engine internally - without you even thinking about it - locks room 123 record from other I/O streams and does the WHERE and UPDATE consideration.

You simply check the ROWCOUNT after the UPDATE to see if 0 or 1 rows were just updated. If 0 rows are updated you know someone else sneaked in and grabbed that room - flash a message to the user to move a bit faster next time

In my opinion your inventory issue requires re-thinking having a "stored total" for the inventory on hand. Usually a better approach is a transaction-like table that puts positive and negative values for inventory into rows as they are hit against. The SUM() of the values is the AMOUNT on HAND.

[jonathan_giuliano]

I think I'm gonna mark this resolved
thanx guys