    23  

        

        (concurrency control)  
     .  
       :   
    .    
      , 
        , 
  .       
,         
      ,   
    . 

      

      (transaction isolation)   ,  
          
  .  Delphi      
 ,      .  
   (TIL  Transaction Isolation Level) 
       , 
    . 
     Delphi       
   ,     
          
.      ,    
    TDatabase    Passthrough 
SQL.      TDatabase  
,    ,  Delphi "" 
   . 

         

       ,  InterBase,   
   .  ,    
,       . 
         
   .      
    " . 
           
  .   "   "  
         
 .          
,  ,      . 
           
  ,     ,  
  . 

     23.     437 

        

    ,      ,  
     . 
        (dirty read). ,    
  ,   ,      
.      ,  
,     ,   . 
        (nonrepeatable read). ,   
         
.   ,    , 
 ,   ,   
.      READ COMMITTED,   
     ,   
,         . 
    Q    (phantom row). ,    
    ,   .  
      READ COMMITTED. 
    Q    (lost update). ,    
 ,  ,    
, . 
    Q     (update side effect).  , 
         ,    
    .   ( ) 
         ,  
   ,      
    - ,       
     .    
  (interleaved) . 
           Translsolation 
 TDatabase         
       .   
     . 

         TDatabase 

          
Translsolation   StartTransaction, Commit  Rollback  TDatabase. 
     Translsolation      
     .   ,  
        
         
   "" ,   . 
     Translsolation    : tiDirtyRead, 
tiReadCommitted  tiRepeatableRead.     
tiReadCommitted.     Translsolation   . 
    Q  tiDirtyRead    ,   
. Q   tiReadCommitted     . 
    Q   tiRepeatableRead  ,      
  , ..      , 
    ,        . 
     StartTransaction  TDatabase    
   ,       . 
            . 
     Commit         
.        save. 
     Rollback         
.        undo. 

    438 IV.    

     Transisoiation      
      ,   Transisoiation 
 TDatabase,    .      
           
.     Transisoiation   
,    ,    . 
     . 23.1    ,  
  Transisoiation,      . 

     23.1.  Transisoiation      

     Transisoiation	InterBase	Oracle	Sybase  Microsoft SQL Server 
    
    tiDirtyRead	Read commited	Read commited	Read commited 

    tiRedCommittited	Read commited	Read commited	Read commited 

    tiRepeatable		Repeatable read	Repeatable read  ,   
 ,     Error 


        SQL 

             
 Passthrough SQL.     SQL,   
    .      
 SQL  InterBase. 
     ,        
SQLPASSTHRUMODE   SHARED AUTOCOMMIT  SHARED NOAUTOCOMMIT  
  ,       
    ,  . 

      

           SQL, 
  SET TRANSACTION. InterBase    
   SNAPSHOT, SNAPSHOT TABLE STABILITY  READ 
COMMITTED.          
,   SQL SET TRANSACTION ISOLATION LEVEL. 
          SQL. 
      SNAPSHOT.      " " 
(snapshot)  .      
 ,    ,  . 
    Q  SNAPSHOT TABLE STABILITY.  ,    
     .     
      . 
    Q READ COMMITTED.        
  ,        
.       
,       . READ 
COMMITTED     . 
        NO RECORD_VERSION (  ).   
      .    SET TRANSACTION 
   WAIT,     ,   
       ,    
 . 
        RECORD_VERSION.      
 ,      _   . 

     23.     439 

             
     ,        
   .    
,  ,     
  .  . 23.2    ,  
    InterBase     . 
     23.2.    InterBase   
    

             
    
     

    SNAPSHOT 

      

         ,   
  

       

       ,   ;  
    ,    

      

         ,       

      

         ,       

       

       ,   ;  
    ,    

    READ COMMITTED 

      

         ,   
  

       

      ""     , 
   

             

      

     ,       
""  ,    

       

      ""     , 
   

    SNAPSHOT TABLE STABILITY       

         ,      

       

           

      

         ,     
 ;        

      

           

       

                 

    440 IV.    

         

       ,      
           
  InterBase.       
  . 
          SNAPSHOT.  
     SNAPSHOT  READ COMMITTED. 
SNAPSHOT TABLE STABILITY       
     .    
   ,      
 . 
      SNAPSHOT  READ COMMITTED  ,   
  ""    . 
    SNAPSHOT,       READ 
COMMITTED.  ,    READ COMMITTED 
   . 

      

       InterBase     SQL 
SET TRANSACTION, COMMIT  ROLLBACK. SET TRANSACTION   
,       
.  COMMIT   ,    Commit  
TDatabase;      save.  ROLLBACK 
   Rollback  TDatabase;   
,    ,     COMMIT. 

    SET TRANSACTION 

          SET TRANSACTION. SET  TRANSACTION 
           ,   
   READ   ONLY. 

    SET TRANSACTION READ ONLY 

         . ,  , 
    .  InterBase SQL  
  ( INSERT, UPDATE H'DELETE)    
  .    InterBase  
  . 

    SET  TRANSACTION   :UpdateCustomers 

     ,     : UpdateCustomers  
    . 
      Sybase Transact-SQL     . 

    BEGIN  TRANSACTION  UpdateCustomers 
    COMMIT  ROLLBACK 

     SQL COMMIT  ,      
, .         
     .  ROLLBACK, , 
  ,  ,   .  
          
 .      ,   
    COMMIT.    ROLLBACK 
   . 

      

        SET TRANSACTION  COMMIT  ROLLBACK 
  ,      .    
         
  SET TRANSACTION.      
    . 

     23.    	441 

     WISQL InterBase    ( 
  InterBase SET TRANSACTION)   .   
   ,       . 
 ,           
   ,   Commit Work  Rollback Work   File. 

       

    ,      ,  , 
    . ,     
         .   
    ,     .   
    ,     , 
   ,      . 
      .     
 ,      ,  
"" (   ),     
,  . 
    ,        ,   
  .   ,      
            .  , 
    ,      
,   ,    (lost 
update)    (update conflict).   
         
    . 
            : 
  .     
     (locking system), 
        
   .      
        . 
      , 
         
   .    
 ,         
           
   . 

       

           
 ,    ,   
,    .  ,  
   ,     
  . 
       ,      
 ,   ,   ,  
        .  
 ,    ,    
   .       
          
 . 
       ,    (SQL Server),   
   ..      
  ,      ,  
  .     (lock 
escalation),  ,     ,   
       ,   
   . ,   Sybase   
       ,  
     200    . 
           ;  
         . 
    ,     
,   . 
       ,      
    .      
  . ,      ,  
    .        
 ,    .    
, ,     ,    
    ,  

    442 IV.    

.  ,      
    . 
    -    ,   
     .   
,   1  ,   2,  
.         , 
   ,         
    . 
      ,  ,    
.    ,     
        
.  Delphi    ,   
        . 
           
      . ,  
     ,    
    . 
    Sybase  Microsoft SQL Server     ,  
   ,  Oracle.      
   ,    .  
    ,      
.        
  .     ,     
,    ,   .  
       
.   , ,   SQL Server,  
  ,   ,    . 

      

     Microsoft SQL Server        
,          
 Oracle,  Sybase      , 
      , 

       

         ,  
       
 ,     .    
     .   
,           
.           
   .       
  ,         
 .       
,     ,    . 
            . 
-,  ,        
.        
 ,    .      
 ,    ,   . 
     ,  ,  ,   . 
       ,   
    ..  ,    
           .  
      . 
        ,  
   .       
,        . 

         Delphi 

     Delphi       
 UpdateMode    TQuery.      
  , Delphi     , 
   ,         
 SQL UPDATE.  UPDATE   WHERE,  
,    .  updateMode ,  
     WHERE. 

     23.    	443 

     UpdateMode      . Q  upWhereAll 
(  )   WHERE    . 
    Q  upWhereChanged   WHERE      
 ,   . 
    Q  upWhereKeyOnly   WHERE      
 (     ,     
    ). 
     ,   TQuery,   UpdateMode.  
    upWhereAll.  ,    
     WHERE,     
 .   ,      
.         
 upWhereChanged.    WHERE,   
    ,   . 
    ,   Delphi    LastName  
CUSTOMER.    upWhereAll    
 SQL (     WHERE). 

    UPDATE CUSTOMER 
    SET LastName='newlastname' 
    WHERE CustomerNumber=l 
    AND LastName='Doe' 
    AND FirstName='John' 
    AND StreetAddress='123 SunnyLane' 
    AND City='Anywhere' 
    AND State='OK' 
    AND Zip='73115' 

       ,    upWhereChanged. 

    UPDATE CUSTOMER SET LastName='newlastname' WHERE CustomerNumber=l AND LastName='Doe' 

     ,    .  ,   
      LastName  
,    WHERE    LastName. 
     LastName     
   ,   ,   
UPDATE,    upWhereChanged,   ,   
. ,     ,   upWhereAll. 
      ,    
,       , ,      
 LastName,    .    UPDATE  
     .     
  . 
     upWhereKeyOnly  UpdateMode   ,    
  .        
 .    ,     
     ,     . 
       , 
     ,       
. ,        
 .   WHERE,    
upWhereKeyOnly,   ,  ,  
 , , ,    .   , 
    ,     
 ,       . 
         upWhereAll,     
    .  upWhereChanged   
    ,    , 
    upWhereAll   .  
upWhereKeyOnly      ,    
           
.     upWhereKeyOnly    
  .         
    ,  , ,  
 ,    .    
  UPDATE     . 
,    upWhereKeyOnly   ,   ,    
     . 

    444 IV.    

        

     -    ,     
          
  ?    -   
?     ,     
  ? 
         :      
    ,       
    .  , ,   
,         .  
      !     
,     ,   ,  
       ,    
        
.       
   ,      
      . 
             
   .        
,    ,  ,   
     . 
    ,       , 
        
/. :    ,    
  . :      
,     .     . 
       ,     
        /,    
    ,   dBASE  Paradox,  
       
,    SQL.      . 
,         CUSTOMER  
 . ,      dBASE, 
        Object Pascal. 

    With taCUSTOMER do begin First; 
    While not EOF do begin FieldByName('LastName').AsString:= 
    Uppercase(FieldByName('LastName').AsString); Next; 
    end; end; 

         ,     
         
.  ,     "  ,  
   ,    .    
   ,    .  
    ,      
StartTransaction  TDatabase. ,   
   .      
   SQL UPDATE    WHERE  
    .   CUSTOMER  ,  
    . 
        ,    
  TQuery  TStoredProc (    
   ).  SQL,    
  CUSTOMER,   . 

    UPDATE CUSTOMER 
    SET LastName=UPPER(LastName) 

           ,   
   ,     .  ,  
         
 . 

     23.    	445 

      ,  ,   
-    SQL DELETE.  ,    
 DELETE,      ,   
    .      
     .    
    ,   DELETE  , 
      .    , 
  dBASE ZAP,     .  
   Sybase  Microsoft SQL Server   TRUNCATE TABLE. 
    ,   DELETE,      
 .         
 ,          
,            
   . 
      Sybase  Microsoft      
     SELECT INTO  INSERT SELECT 
       . ,    
INSERT SELECT,    ,    
 .  SELECT INTO    SQL Server  
          
.        (InterBase, 
,  ), , ,     
  . 
     SELECT  INTO  Transact-SQL  SQL Server   . 

    SELECT LastName, INTO NEWCUSTOMER FROM CUSTOMER 
    FirstName 

               NEWCUSTOMER,        
     LastName    FirstName   CUSTOMER.   
   . 

    CREATE  TABLE  NEWCUSTOMER (LastName   CHAR(30), FirstName  CHAR(30)) 
    INSERT   INTO NEWCUSTOMER   (LastName,   FirstName) SELECT  LastName,   
FirstName FROM  CUSTOMER 

     SELECT INTO  ,      
 ,   . 
    	   ,        
 . 
    Q	           . 
    Q	  ,        . 
       
     ,       
 ,         
.        
      .    
 ,       
.        .  
   .       
    . 
    -,       :  
, UPDATE  DELETE,      UPDATE  
DELETE,        WHERE. 
   .   CUSTOMER   
,            
  . 

    SET TRANSACTION; 
    UPDATE CUSTOMER 
    SET LastName=UPPER(LastName) 
    WHERE CustomerNumber between 1 and 100000; 
    COMMIT; 

    446 IV.    

    SET TRANSACTION; 
    UPDATE CUSTOMER 
    SET LastName=UPPER(LastName) 
    WHERE CustomerNumber between 100001 and 200000; 
    COMMIT; 
    SET TRANSACTION; 
    UPDATE CUSTOMER 
    SET LastName=UPPER(LastName) 
    WHERE CustomerNumber between 200001 and 300000; 
    COMMIT; 

            . 
    -,    ,   UPDATE  DELETE 
   .       (SQL Server, 
, , a InterBase  ). 
            , 
   ,   UPDATE  
DELETE.    UPDATE  DELETE  ,  
    .      
Transact-SQL  SQL Server. 

    SET ROWCOJINJL 50000 
    /*   50000  ,   UPDATE . 
    */ 
    WHILE (EXISTS (SELECT * FROM ORDERS WHERE AmountoO)) 
    BEGIN 
    UPDATE  ORDERS   SET  Amount=0 
    WHERE AmountoO   /*   UPDATE     .   */ END 

         ,     
  .      ,    
WHERE  UPDATE   .  ,   
Amount    <>0,     
UPDATE  Amount   0.  ,  UPDATE 
    .       . 
        DELETE. 

    SET ROWCOUNT 50000 
    /*    DELETE 50000 . */ 
    WHILE (EXISTS (SELECT * FROM ORDERS WHERE Amount=0)) 
    BEGIN 
    DELETE FROM ORDERS 
    WHERE Amount=0 END 


     

     ,        
   .     Delphi 
    /    
  Translsolation  UpdateMode.   ,   
,  Delphi,      
 ,     
/    ,    
   .      
    . 

     ... 

           SQL.     
     ,   , 
,   .. 

     23.    	447 
