CF and Stored Procedures.pptVIP

CF and Stored Procedures Lei Wang ALP International Main Topics Database Locks How Cold Fusion perform DB locks Stored Procedure Stress test to compare Cftransaction and stored procedure How to use stored procedure Common Mistakes Database Locks “Lost update problem” Multiple users accessing the same data Database Locks Blocking access for all other users Types of Locks Table-level: supported by all database. The entire table is locked when a user is posting a transaction that utilizes a row from that table Row-level: supported by higher-end database. Only records being utilized by a transaction are locked. How Cold Fusion perform DB Locks Use CFTRANSACTION to group multiple queries into a single unit. CFTRANSACTION also provides commit and rollback processing. CFTRANSACTION ACTION=BEGIN or COMMIT or ROLLBACK ISOLATION=Read_Uncommitted or Read_Committed or Repeatable_Read /CFTRANSACTION CFLOCK Standard usage of cftransaction(from CF Studio) CFTRY CFSET commitIt = Yes CFTRANSACTION ACTION=BEGIN CFQUERY NAME=makeNewCourse DATASOURCE=cfsnippets INSERT INTO Courses (Number, Descript) VALUES (#myNumber#, #myDescription#) /CFQUERY CFCATCH TYPE=DATABASE CFTRANSACTION ACTION=ROLLBACK/ CFSET commitIt = No /CFCATCH CFIF commitIt CFTRANSACTION ACTION=COMMIT/ CFQUERY ……………………………………… /CFQUERY /CFTRANSACTION /CFTRY Ending tag within the cftry block should be a cfcatch tag Standard usage of cftransaction CFTRANSACTION ACTION=“BEGIN” CFTRY CFQUERY NAME=makeNewCourse DATASOURCE=cfsnippets INSERT INTO Courses (Number, Descript) VALUES (#myNumber#, #myDescription#) /CFQUERY CFQUERY ……………………………………… /CFQUERY CFTRANSACTION ACTION=COMMIT/ CFCATCH TYPE=DATABASE CFTRANSACTION ACTION=ROLLBACK/ /CFCATCH /CFTRY /CFTRANSACTION How DB tools perform Lock Transaction A logical unit of work. Begin the transaction Commit the transaction or rollback transaction Standard usage of transaction BEGIN TRANSACTION