SQL - LOCKS TUTORIALS

Locks are the mechanisms used to prevent destructive interaction between users accessing same resource simultaneously. Locks provides high degree of data concurrency.

TYPES

Row level locks

Table level locks

ROW LEVEL LOCKS

In the row level lock a row is locked exclusively so that other cannot modify the row until the transaction holding the lock is committed or rolled back. This can be done by using select..for update clause.

Ex:

SQL> select * from emp where sal > 3000 for update of comm.;

TABLE LEVEL LOCKS

A table level lock will protect table data thereby guaranteeing data integrity when data is being accessed concurrently by multiple users. A table lock can be held in several modes.

Share lock

Share update lock

Exclusive lock

SHARE LOCK

A share lock locks the table allowing other users to only query but not insert, update or delete rows in a table. Multiple users can place share locks on the same resource at the same time.

Ex:

SQL> lock table emp in share mode;

Read More

How to test data loading in Data base testing?

1    1). How to test data loading in Data base testing

Using with Query analyzer.
You have to do the following things while you are involving in Data Load testing.
1. You have know about source data (table(s), columns, data types and Constraints)
2. You have to know about Target data (table(s), columns, data types and Constraints)
3. You have to check the compatibility of Source and Target.
4. You have to Open corresponding DTS package in SQL Enterprise Manager and run the DTS package (If you are using SQL Server).
5. Then you should compare the column's data of Source and Target.
6. You have to check the number to rows of Source and Target.
7. Then you have to update the data in Source and see the change is reflecting in Target or not.
8. You have to check about junk character and Nulls.
2). What is way of writing test cases for database testing?
You have to do the following for writing the database test cases.
1. First of all you have to understand the functional requirement of the application thoroughly.
2. Then you have to find out the back end tables used, joined used between the tables, cursors used (if any), triggers used (if any), stored procedures used (if any), input parameter used and output parameters used for developing that requirement.
3. After knowing all these things you have to write the test cases with different input values for checking all the paths of SP.
One thing writing test cases for backend testing not like functional testing. You have to use white box testing techniques.or not (for performance related issues)
4. Stored procedures.
5.The field size defined in the application is matching with that in the db.
3). What SQL statements have you used in Database Testing?

            DDLDDL is Data Definition Language statements. Some examples: · CREATE · ALTER - · DROP -· TRUNCATE -· COMMENT - · RENAME - DMLDML is Data Manipulation Language statements. Some examples: · SELECT - · INSERT - · UPDATE - · DELETE - · MERGE - UPSERT -· CALL - · EXPLAIN PLAN - · LOCK TABLE - DCLDCL is Data Control Language statements. Some examples: · GRANT - · REVOKE - · COMMIT - · SAVEPOINT - · ROLLBACK - COMMIT -· SET TRANSACTION - This are the Database testing commands.

4). How to use SQL queries in WinRunner/QTP
In QTP
Using output database check point and database check point,
Select SQL manual queries option
And enter the "select" queries to retrieve data in the database and compare
 The expected and actual.

5). What steps does a tester take in testing
Stored Procedures?

           In my view, the tester has to go through the requirement, as to why the particular stored procedure is written for? And check whether all the required indexes, joins, updates, deletions are correct comparing with the tables mentions in the Stored Procedure.  And also he has to ensure whether the Stored Procedure follows the standard format like comments, updated by, etc.

Read More