I have a report that reads the Stock Level from the Item Table; I would like to prevent users from modifying the Stock Level while I run my report; The Database resides on a SQL box; The report does some kind of allocation Stock to Sales Orders; In the past we had the Report property set to “UpdateNoLocks” We had results where the Report would allocate fractions; My thought was that someone could alter the stock level while allocations take place, because of the locking type used. Is this a false statement ? or would the Report see a Snapshot of the Database when using “UpdateNolocks” ? -------------------- I have set the report property to “Update”; I have read the help and it is unclear to me; Here is what I understand:: The update property says a transaction starts as soon as a write is performed; Does this mean there is no lock on the table until a write transaction takes place? Does this mean ANY write transaction to ANY table? I am more concerend about the Read View; UpdateNolocks Suggests that the Read is the same as the Bowse: “This means that it is possible to read uncommitted data.” Does this mean there is not a SNAPSHOT view of the Database ? ------------------- IF i do this “record1.LOCTABLE”; does this mean I do not have to do “record2.LOCKTABLE” if both variables point to the same “Table” ? [:D] Thankx
‘UpdateNoLocks’ - the default - means that until you lock a table the table will be read without placing any SQL locks on it, and as you say this can access non-committed data. Once it is locked any read of the table afterwards will place SQL UPDATE locks, meaning that no other sessions will be able to read or update the table until the end of transaction when the locks are released. This applies to all tables accessed within your transaction, or running report in this case. It therefore allows inaccuracies because tables that do not get locked can be changed by other sessions - it is not a consistent snapshot. The ‘Update’ transaction type, however, places SQL SHARE locks on a table as soon as it is read - it can be read by another session but not updated. When the table is locked the same applies as above - UPDATE locks are placed as soon as it is first accessed, which is not possible if another session has already placed SHARE or UPDATE locks on it. This is therefore a consistent snapshot, although the snapshot is achieved by locking records which is a concurrency issue, and not by maintaining prior record versions as the Navision Server does. The ‘Snapshot’ type is the same as for ‘Update’ except you cannot perform modifications within the transaction; it is read only. SQL Server 2000 does not provide a non-locking snapshot isolation level, which is why all these transaction types exist. SQL Server 2005 will provide such isolation, in a similar way to Navision Server. You’re right in your last assumption - all variables representing the same table will lock that table. But note that against SQL Server LOCKTABLE does not actually place any locks - UPDATE lock are only placed on records that you read after the LOCKTABLE.