Systems Engineering and RDBMS

Archive for February 1st, 2007

Securing Web Applications

Posted by decipherinfosys on February 1, 2007

It used to be that an organization’s network-layer infrastructure was the target of most exploits and attacks. Due to the growing popularity of web and web services applications, however, organizations are faced with a whole new realm that requires protection. These complex and often times much exposed web-based solutions have become an essential part of any e-Business infrastructure. The importance of security for these solutions is obvious, but how can it be done effectively? 

 

Common Attacks

Buffer Overflow – This is an input validation attack that overflows a buffer with excessive data. If a buffer overflow attack is successful, a cracker can gain privileges on a system that are identical to those required to run the applications on that system. Two well-known examples of this type of attack are Code Red and Nimda. 

SQL Injection – This is also an input validation attack, which sends SQL commands to a web application. These commands are then relayed the application’s database. Once a cracker has established access to sensitive information in your database the potential damage can be excessive. 

Cross-Site Scripting – This is an attack where an end-user accidentally, and unknowingly, executes malicious code created by a cracker to gain elevated privileges to a secure web application. Successful cross-site scripting attacks often result in the identity theft of the end-user, and often times the end-user is unaware of the theft for long period of time.

Protect the Infrastructure and Users

Total security requires protection of all aspects of an application’s infrastructure, including operating systems, databases, as well as the application itself. Unfortunately, security requirements are often not comprehensively defined. For instance, some organizations implement one firewall and a few intrusion detection devices on their network and think they are safe. That is about as far from the truth as one can get. Each individual machine, every email, and any other piece of the application infrastructure needs to have some kind of security measures assigned to it. Also, one of the most important yet often the most overlooked item is the human factor. An organization’s users must be educated to be security-aware each and every day. Users need to understand how important their knowledge is to the organization.

Deploy Web Application Firewalls

As previously stated, many organizations feel secure after implementing a few intrusion detection systems throughout their network. A network IDS solution is very limited in the protection it can offer, which is basically inspecting IP packets for proper configuration and validating that the headers contain the appropriate information. These devices are incapable of analyzing the HTML data payload, and it is this capability that would be necessary to effectively defend web applications from being compromised. To properly secure a web-based application an organization should implement what are known as web firewalls. Unlike IDS systems, which work at the network-layer, web firewalls operate at the application layer. These firewalls have the ability to take apart the HTML data payload, and inspect the actual HTML communication involving client requests and application responses. This protects the actual web application from falling victim to things like malicious scripts being embedded within the HTML code, something that a network-layer IDS of firewall has no prayer of detecting.

Protect the SSL

It is ironic that, considering its secure and protective intentions, that the Secure Sockets Layer (SSL) has become a tool for crackers to avoid detection. SSL is the ubiquitous security mechanism for e-Commerce web sites, and its widespread user and acceptance only complicates the security issue even more. These days the most novice cracker has the ability to establish an SSL session with a web application, and this secure session provides the cracker a tunnel through which to launch attacks against the application. These attacks will not only go completely unnoticed, but since it is also encrypted, the malicious SSL traffic will be forwarded by firewalls and IDS systems with no questions asked. Analyzing SSL traffic for security purposes is only possible if the data is decrypted.

Hide the Application’s Information

Many malicious attacks are customized to exploit known security vulnerabilities in operating systems and applications. An attacker’s life is made much easier if it is possible to gather information about an organization’s application infrastructure prior to launching the attack. This allows the attacker to target specific assets of the organization, which keeps the footprint of the attack as small as possible, thereby reducing the chances of the attack being detected.

There are steps that can be taken to minimize or “hide” a web application’s information. The more pieces of the puzzle that remain a mystery to the cracker the better: 

  • Remove as many server response headers as possible. This will make it more difficult to determine the type of web server being used.
  • Encrypt as much information as possible. For example, cookies and URLs
  • Remove HTML comments. I know, I know, this goes completely against what developers have always been told – to document as much as possible. However, many comments in application code can be dead giveaways about the application, and even worse, may contain sensitive information such as database connection details. Keep comments to a minimum within the HTML, and keep the details in a separate document.

These are just some of a number of things administrators, developers, and end-users can do to protect their organization’s data. E-Commerce applications and web services are becoming increasingly complex, and organizations are becoming so dependent on the technologies that a single failure within the infrastructure could spell disaster. It is essential for companies to remain informed of the latest threats and security solutions, and they should be willing to make the necessary investments to implement a comprehensive, multi-layered, security solution.

Posted in Security | Leave a Comment »

Conditional Uniqueness in Oracle using FBI

Posted by decipherinfosys on February 1, 2007

We were presented a very typical problem by one of our clients.  They have a store in which they define the different locations.  These locations can be classified into different categories – Reserve locations, Active locations, etc..  According to them the uniqueness of a record in their table varied depending upon the location category i.e. if it is an active location, then the combination of the first three attributes forms the alternate key, if the location category is reserve, then a combination of a separate set of attributes makes the record unique and so on and so forth.  And since they had a legacy system, they were storing all these in a single physical table.  We were clearly told that the re-design and normalization of this structure is out of question since that would involve a lot of application changes.  But we did need to implement a way of maintaining the data integrity i.e. do not allow duplicates into the table based on the location categories.

This client was using Oracle and Oracle has a very neat feature called : FBI (Function Based Indexes).  We have posted about it in the past as well – you can search for it on our blog or on our site. So, let’s see how we can enforce “conditional” uniquess using a FBI in Oracle.

CREATE UNIQUE INDEX LOCATION_UX_IND_1 ON LOCATION (CASE WHEN Location_Category = ‘A’ THEN POSITION||AISLE||AREA
WHEN Location_Category = ‘O’ THEN POSITION||AISLE||BAY||AREA
WHEN Location_Category = ‘Q’ THEN AREA||ZONE
WHEN Location_Category = ‘R’ THEN POSITION||AISLE||BAY
WHEN Location_Category = ‘S’ THEN POSITION||BAY||AREA
ELSE NULL
END)
TABLESPACE LARGETBS
PCTFREE 15
INI_TRANS 40;

This served the business needs without requiring a re-design.  And since this table has a static list of locations in which the records get updated only very rarely, this approach was fine for performance as well.

Posted in Oracle | Leave a Comment »

Open_Cursors parameter in Oracle

Posted by decipherinfosys on February 1, 2007

The total allowable open cursors per session is dependent upon and should not exceed open_cursors db initialization parameter setting. The value for this init parameter can be inquired, by using:

SELECT value FROM v$parameter WHERE name = ‘open_cursors’;

Here are two scripts that you can use to see whether you have cursor leaks in your system and whether additional information pertaining to those cursors:

/****************************************************************************************
SQL 1: Shows the Program name and sqltext for open cursors in active sessions.
—       It will not show information about the session you are currently logged
—       in though – if you want that, comment out the line mentioned in the code below.
—       This will output to the file Program_Cursor_SQL.txt in the directory
—       this script is run from.
*****************************************************************************************/

SPOOL Program_Cursor_SQL.txt
SET linesize 200 pages 100 feed ON verify OFF echo OFF
COLUMN Program   heading ‘Program’ format a48 wrap
COLUMN SQL       heading ‘SQL|Text’    format a64 wrap
break ON Program skip 2
SELECT /*+rule */
ses.Program AS Program,
sqltxt.sql_text SQL
FROM  v$open_cursor ocur,
v$session ses,
v$sqltext sqltxt
WHERE ocur.sid = ses.sid
AND ses.Status = ‘ACTIVE’            — Only active sessions.
AND ses.username = ????              — Use ses.username IS NOT NULL for all users.
AND ((ses.sql_address = sqltxt.ADDRESS AND  ses.sql_hash_value = sqltxt.hash_value)
OR
(ses.prev_sql_addr  = sqltxt.ADDRESS AND  ses.prev_hash_value = sqltxt.hash_value))
AND ocur.ADDRESS= sqltxt.ADDRESS AND  ocur.hash_value = sqltxt.hash_value
/*Don’t include this session (comment out if you wish to include your session)*/
AND ses.sid != (SELECT sid FROM v$session WHERE audsid=USERENV(‘sessionid’))
ORDER BY ses.sid, ses.serial#,ses.process, ses.username,ses.program,sqltxt.piece
/
clear breaks
clear COLUMNS
SPOOL OFF

/****************************************************************************************
— SQL 2: This script is used to report the total number of open cursors for
—        the currently open user sessions (active/inactive) in the database
—        by USERNAME.
*****************************************************************************************/

SET pages 1000 lines 200 feed OFF echo OFF
COLUMN username    heading ‘DB User’ format a30
COLUMN act_sess    heading ‘Active|Sessions’ format 9,999
COLUMN inact_sess  heading ‘Inactive|Sessions’ format 9,999
COLUMN tot_sess    heading ‘Total|Sessions’ format 99,999
COLUMN act_curs    heading ‘Active|Cursors’ format 999,999
COLUMN inact_curs  heading ‘Inactive|Cursors’ format 999,999
COLUMN tot_curs    heading ‘Total|Cursors’ format 9,999,999
break ON report
compute SUM label ‘Total Count:’ OF tot_sess tot_curs ON report
SELECT username,
act_sess,
inact_sess,
(act_sess+inact_sess) AS tot_sess,
act_curs,
inact_curs,
(act_curs+inact_curs) AS tot_curs
FROM
(SELECT s.username,
(SELECT COUNT(*) FROM v$session ss WHERE ss.username = s.username AND ss.status = ‘ACTIVE’) AS act_sess,
(SELECT COUNT(*) FROM v$session ss WHERE ss.username = s.username AND ss.status = ‘INACTIVE’) AS inact_sess,
COUNT(DECODE(s.status, ‘ACTIVE’, oc.sid)) AS act_curs,
COUNT(DECODE(s.status, ‘INACTIVE’, oc.sid)) AS inact_curs
FROM v$open_cursor oc inner join v$session s ON oc.sid = s.sid
WHERE s.username NOT IN (‘SYS’, ‘SYSTEM’)
GROUP BY s.username)
ORDER BY tot_curs DESC
/
clear breaks
clear COLUMNS

Posted in Oracle | Leave a Comment »

OTLT (One True Lookup Table)

Posted by decipherinfosys on February 1, 2007

I have seen many organizations using a one true lookup table for look-ups. What OTLT means is that one has a table to do all the code and system related look-ups in the schema rather than having n number of look up tables. This is an OO design pushed into the database tier and poses many problems including :

a) Not being able to force foreign keys (so your data integrity is compromised right there),

b) Not being able to use the right data-types since everything has to be a string in order to accomodate all data-type values (this itself can give rise to bad data issues since a numeric(4,2) constraint cannot be enforced –> thus domain integrity is out of the door,

c) Because of (b), the length for the string column will be huge,

d) One has to then write complex SQL queries in order to retrieve the data and also take into account the type conversions that occur when you join this table with the other tables…implicit conversion is not allowed in all RDBMS and is not a good thing anyways.

When you end up with tons of look up data, this really becomes very cumbersome – though in smaller systems, this issue gets masked. One of the reasons people tend to shy away from a bunch of look up tables is because of maintenance but if you look at it, it is really not an issue and does give you a lot of benefits including the first and foremost, it preserves data integrity. If you need to have a screen to be able to modify data in those look up tables, you can create a view that covers all those look-up tables and then make that view updateable using SQL code. That way, the end users have one place to go in order to make the configuration lookup data changes and in the back-end, you are preserving data integrity and the above mentioned issues won’t arise either.

Here is an excellent article on OTLT by database guru: Joe Celko.

http://www.dbazine.com/ofinterest/oi-articles/celko22

Posted in Data Model | 2 Comments »

Surrogate Keys vs Natural Keys for Primary Key?

Posted by decipherinfosys on February 1, 2007

This topic probably is one of those that you cannot get any two database developers/DBAs to agree upon.  Everyone has their own opinion about this and it is also one of the most discussed topics over the web when it comes to data modeling.  Rather than taking any side :-), we are just listing out our experiences when it comes to chosing between a surrogate key vs the natural keys for the tables.

Surrogate Key:

Surrogate keys are keys that have no “business” meaning and are solely used to identify a record in the table.  Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like generated via a table in the schema).

Natural Key: 

Keys are natural if the attribute it represents is used for identification independently of the database schema.  What this basically means is that the keys are natural if people use them example: Invoice-Numbers, Tax-Ids, SSN etc.

Design considerations for choosing the Primary Key:

Primary Key should meet the following requirements:

  1. It should be not null, Unique and should apply to all rows.
  2. It should be minimal (i.e. less number of columns in the PK: ideally it should be 1, if using composite keys, then make sure that those are surrogates and using integer family data-types).
  3. It should be stable over a period of time (should not change i.e. update to the PK columns should not happen).

Keeping these in mind, here are the pros and cons of Surrogate vs. Natural keys:

Surrogate Key

I prefer surrogate keys to be DB controlled rather than being controlled via a next-up table in the schema since that is a more scalable approach.

Pros:

  1. Business Logic is not in the keys.
  2. Small 4-byte key (the surrogate key will most likely be an integer and SQL Server for example requires only 4 bytes to store it, if a bigint, then 8 bytes).
  3. Joins are very fast.
  4. No locking contentions because of unique constraint (this refers to the waits that get developed when two sessions are trying to insert the same unique business key) as the surrogates get generated by the DB and are cached – very scalable.

Cons:

  1. An additional index is needed.  In SQL Server, the PK constraint will always creates a unique index, in Oracle, if an index already exists, PK creation will use that index for uniqueness enforcement (not a con in Oracle).
  2. Cannot be used as a search key.
  3. If it is database controlled, for products that support multiple databases, different implementations are needed, example: identity in SS2k, before triggers and sequences in Oracle, identity/sequence in DB2 UDB.
  4. Always requires a join when browsing the child table(s).

Natural Key

 Pros:

  1. No additional Index.
  2. Can be used as a search key.

 Cons:

  1. If not chosen wisely (business meaning in the key(s)), then over a period of time additions may be required to the PK and modifications to the PK can occur.
  2. If using strings, joins are a bit slower as compared to the int data-type joins, storage is more as well.  Since storage is more, less data-values get stored per index page.  Also, reading strings is a two step process in some RDBMS: one to get the actual length of the string and second to actually perform the read operation to get the value.
  3. Locking contentions can arise if using application driven generation mechanism for the key.
  4. Can’t enter a record until value is known since the value has some meaning.

 Choosing Surrogate vs. Natural Keys:

There is no rule of thumb in this case. It has to be evaluated table by table:

  1. If we can identify an appropriate natural key that meets the three criteria for it to be a PK column, we should use it.  Look-up tables and configuration tables are typically ok.
  2. Data-Type for the PK: the smaller the better, choose an integer or a short-character data type.   It also ensures that the joins will be faster.  This becomes even more important if you are going to make the PK as a clustered index since non-clustered indexes are built off the clustered index.  RDBMS processes integer data values faster than the character data values because it converts characters to ASCII equivalent values before processing, which is an extra step.

Posted in Data Model | 19 Comments »

Locking trace flags in SQL Server

Posted by decipherinfosys on February 1, 2007

There are a few trace flags that can help you fine tune your locking and find deadlock problems. Please use them with extreme caution as they can generate a lot of overhead in your system (depends upon the nature of the application) – and do remember to turn these flags off after you have collected the data that you needed.

1200: Shows all locks acquired by all connections. This provides a huge output and so our recommendation would be to use this in a controlled test environment, where only a single connection is working at the same time.

1204 & 3605: Outputs a lot of information about the participants in a deadlock. These are the most commonly used locking trace options and are very helpful in troubleshooting deadlock and deadlock chain issues.

1211: Disables all lock escalation. This trace flag forces the lock manager not to escalate locks, even if it’s running out of locks. There are other better ways to prevent lock escalation and we will cover them either in a new blog post or a whitepaper.

1224: This is a SQL Server 2005 specific trace flag. It disables lock escalation of individual statements, and specifies that the engine operates as if the sp_configure’s “locks” option is set to 0. The engine only escalates row or page locks to table locks if the amount of memory used by the locked resources exceed 40% of the memory used by the engine (exclusive of AWE (Address Windows Extension) memory).

Note: If both trace flag 1211 and 1224 are set, 1224 takes precedence over 1211.
Use of this trace flag can generate excessive numbers of locks. This can slow the performance of the engine, or cause 1204 errors (unable to allocate lock resource) due to insufficient memory so use it with extreme caution.  The most common ones that we have used are 1204 and 3605.

Posted in SQL Server | 1 Comment »

Windows Vista Security – Part I

Posted by decipherinfosys on February 1, 2007

The release of Windows Vista presents a whole new set of challenges for IT administrators and end-users alike. The overall goal of the design and functionality of the operating system is to simplify the user experience, but Microsoft has changed enough of the ‘look and feel’ to make it difficult for seasoned Windows veterans to find their way around initially. In addition, there many new security features bundled with Vista, and it will take some time to become familiar with all of these. There will be many blogs posted here that will address various pieces of the Vista security stack. This initial posting will give a brief overview of some of the new security features, and subsequent entries will explore each in greater detail.

BitLocker

BitLocker encrypts files so that they are rendered unreadable if a laptop or PC is stolen. BitLocker also has built-in logic that prevents files from being encrypted if the system appears to have been stolen or tampered with. Users have the option to lock the boot process with a PIN number or by attaching a USB thumb drive with the key to decrypt files.

Windows Firewall

There are many new features and capabilities in the newest version of Windows Firewall. These have already been addressed in the following blog entry:

https://decipherinfosys.wordpress.com/2007/01/25/enhancements-in-windows-firewall

Network Access Protection

This security feature allows administrators to customize policies that relate to client machines on a shared network. These policies can included certain minimum requirements such as operating system patch levels, firewall settings, and antivirus software to name but a few. If the client machine requesting access does not meet these specified requirements, the machine is either denied network access, or is placed in a ‘quarantined’ area of the network isolated from other machines. Full network access is granted once the client machine has been updated with whatever is required to meet that particular network’s policy requirements.

Software Protection Platform

Microsoft is increasing their efforts to reduce the circulation of pirated copies of their operating system. If Microsoft discovers an illegal copy of Vista, which can be accomplished via something as simple as connecting to Microsoft’s website to download a patch or piece of software, the operating system switches over to a mode of significantly reduced functionality. Most services and features will be rendered inoperable, but basic security functionality, like the ability to download updates, is retained.

‘Forefront’ Security Protection

Forefront is Microsoft’s answer to antivirus and anti-spyware solutions in a all-inclusive package. Forefront is designed to us Active Directory and Windows Server Update services to distribute its security updates and virus definition files. Forefront will offer a number of sub-components that are intended to protect Microsoft applications. Two examples of these sub-components are:

· Forefront Security for Microsoft Exchange

· Forefront Security for Microsoft SharePoint

Forefront is currently still in testing, and is tentatively scheduled to be released later this year.

Vista User Account Control

User Account Control allows for highly granular control over user accounts, reducing the need to grant users Administrator or Power User privileges. Most applications and processes can be run with minimal privileges, but these permissions can be temporarily elevated so that certain administrative tasks and application functions can be performed. Once these have been completed the privileges level will revert back to its original state.

Another feature of User Account Control is that when a task requires elevated privileges, like installing an application, a pop-up box will appear advising the user of the issue and asks if it is okay to proceed. At this point the user can simply click “Allow”, and the task will continue as normal. You can expect to see a large number of these pop-up notifications during the initial installation and configuration of a machine running Vista. There are advantages to this control, the main one being that is that it helps to prevent users from accidentally making changes to their machines. It also helps to mitigate the damage done by malware.

Posted in Windows Vista | Leave a Comment »

Lock Detection Scripts in SQL Server 2005

Posted by decipherinfosys on February 1, 2007

In SQL Server 2005, the dynamic management views like sys.dm_tran_locks, sys.dm_os_tasks etc. have added much more information to troubleshoot locking issues.

You can use the following query to associate a SPID value with a Windows thread id. You can then monitor the performance of the thread in the Windows Performance Monitor. This query does not return SPIDs that are currently sleeping.

SELECT OSTasks.session_ID, OSThreads.os_thread_id
FROM sys.dm_os_tasks AS OSTasks
INNER JOIN sys.dm_os_threads AS OSThreads
ON OSTasks.worker_address = OSThreads.worker_address
WHERE OSTasks.session_ID IS NOT NULL
ORDER BY OSTasks.session_ID;
GO

The following example returns all of the locks held by the current session:

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

This SQL can be used to get more information on the locks. It joins a couple of dynamic management views and also sys.sysprocesses. You can play around with these and chose the columns and the information that makes the most sense for your environment:

SELECT DISTINCT
SP.SPID,
SP.HOSTPROCESS,
SP.LOGIN_TIME,
SP.LAST_BATCH,
SP.OPEN_TRAN,
SP.STATUS,
SP.HOSTNAME,
SP.PROGRAM_NAME,
SP.CMD,
SP.LOGINAME,
SP.CPU,
SP.MEMUSAGE,
A.RESOURCE_TYPE, A.RESOURCE_SUBTYPE,
A.RESOURCE_ASSOCIATED_ENTITY_ID,
(CASE A.REQUEST_MODE
WHEN ‘SCH-S’ THEN ‘SCHEMA STABILITY’
WHEN ‘SCH-M’ THEN ‘SCHEMA MODIFICATION’
WHEN ‘S’ THEN ‘SHARED’
WHEN ‘U’ THEN ‘UPDATE’
WHEN ‘X’ THEN ‘EXCLUSIVE’
WHEN ‘IS’ THEN ‘Intent Shared’
WHEN ‘IU’ THEN ‘Intent Update’
WHEN ‘IX’ THEN ‘Intent Exclusive’
WHEN ‘SIU’ THEN ‘Shared Intent Update’
WHEN ‘SIX’ THEN ‘Shared Intent Exclusive’
WHEN ‘UIX’ THEN ‘Update Intent Exclusive’
WHEN ‘BU’ THEN ‘Bulk Update’
WHEN ‘RangeS_S’ THEN ‘Shared Key-Range and Shared Resourcelock’
WHEN ‘RangeS_U’ THEN ‘Shared Key-Range and Update Resource lock’
WHEN ‘RangeI_N’ THEN ‘Insert Key-Range and Null Resourcelock’
WHEN ‘RangeI_S’ THEN ‘Key-Range Conversion lock, created by an overlap of RangeI_N and S locks’
WHEN ‘RangeI_U’ THEN ‘Key-Range Conversion lock, created byan overlap of RangeI_N and U locks’
WHEN ‘RangeI_X’ THEN ‘Key-Range Conversion lock, created byan overlap of RangeI_N and X locks’
WHEN ‘RangeX_S’ THEN ‘Key-Range Conversion lock, created byan overlap of RangeI_N and RangeS_S locks’
WHEN ‘RangeX_U’ THEN ‘Key-Range Conversion lock, created byan overlap of RangeI_N and RangeS_U locks’
WHEN ‘RangeX_X’ THEN ‘Exclusive Key-Range and ExclusiveResource lock’
ELSE NULL
END) AS REQUEST_LOCK_MODE,
A.REQUEST_TYPE,
A.REQUEST_STATUS,
A.REQUEST_OWNER_TYPE,
C.NAME,
C.TRANSACTION_BEGIN_TIME,
C.TRANSACTION_TYPE,
C.TRANSACTION_STATE,
C.TRANSACTION_STATUS,
C.TRANSACTION_STATUS2,
C.DTC_STATE,
C.DTC_ISOLATION_LEVEL,
DB_NAME(SP.DBID) DATABASE_NAME
FROM
SYS.DM_TRAN_LOCKS A
INNER JOIN SYS.SYSPROCESSES SP
ON A.REQUEST_SESSION_ID = SP.SPID
LEFT OUTER JOIN SYS.DM_EXEC_REQUESTS B
ON A.REQUEST_REQUEST_ID = B.REQUEST_ID
LEFT OUTER JOIN SYS.DM_TRAN_ACTIVE_TRANSACTIONS C
ON A.REQUEST_OWNER_ID = C.TRANSACTION_ID
WHERE SP.SPID > 50 /*REMOVING THE SYSTEM SPIDS*/
AND SP.PROGRAM_NAME NOT LIKE ‘SQLAGENT%’
ORDER BY SPID
GO

Posted in SQL Server | Leave a Comment »

Simulating autonomous transaction behavior in SQL Server

Posted by decipherinfosys on February 1, 2007

For the projects that are converting from Oracle to SQL Server, at times you will face an issue wherein the code that was written in Oracle is using pragma autonomous transactions.  What this means is that even though the transaction is a nested transaction, it’s commit/rollback scope is totally separate.  This is particularly helpful in scenarios where auditing is required or if one needs to log error messages despite the transaction doing a rollback.

In SQL Server, there is no direct equivalent for this.  Moreover, in SQL Server, if you have a nested transaction, one needs to be aware of the fact that the outermost commit is what controls the inner commits as well and any of the inner rollback will rollback all the nested transactions as well.  In order to simulate the same autonomous transaction behavior in SQL Server, one can make use of the table variables.  Here is such an example:

CREATE TABLE DEMO_AT (COL1 INT NOT NULL PRIMARY KEY, COL2 VARCHAR(10))
GO
CREATE TABLE MSG_LOG (MSG_LOG_ID INT IDENTITY NOT NULL PRIMARY KEY, MSG VARCHAR(100), VALUE VARCHAR(10))
GO

INSERT INTO DEMO_AT VALUES (1, ‘TEST’)
GO

BEGIN TRAN
DECLARE @TEST TABLE (COL1 INT, COL2 VARCHAR(10))
INSERT INTO @TEST SELECT * FROM DEMO_AT

–Now insert the value into the DEMO_AT table
–it will fail because of the PK violation
INSERT INTO DEMO_AT SELECT * FROM @TEST

–just use the @@error for demos
–in real life, use Try/Catch block
if @@error <> 0
begin
ROLLBACK TRAN
end
else
begin
COMMIT TRAN
end

–log it after the rollback occurs
insert into msg_log (msg, VALUE) select ‘INSERT FAILED’, COL2 FROM @TEST

–Now, see the value
SELECT * FROM MSG_LOG

Here is the output of that run:

Server: Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint ‘PK__DEMO_AT__483BA0F8’. Cannot insert duplicate key in object ‘DEMO_AT’.
The statement has been terminated.

MSG_LOG_ID  MSG                                                                                                  VALUE
———– —————————————————————————————————- ———-
1           INSERT FAILED                                                                                        TEST

So, how/why did this work even after the rollback was done?  Since we are using a table variable over here which is memory resident and it’s scope is not for that transaction, one can still access it’s contents after the rollback is done.  This technique can be used in auditing scenarios or for error handling in situations where a rollback occurs and you still want to log the messages into some table for further troubleshooting.

Posted in Oracle, SQL Server | 1 Comment »

Blocking on Foreign Keys in SQL Server

Posted by decipherinfosys on February 1, 2007

We have seen that this is one area that people tend to neglect when it comes to blocking locks.  Let’s illustrate this using an example:

Let’s consider 2 tables TABLEA and TABLEB and that there is a foreign key relationship between the two as defined below:

Create table TableA (Col1 int, Col2 varchar(10),

constraint PK_TABLEA primary key (Col1))
GO
Create table TableB (Col1 int, Col2 int, Col3 varchar(10),

constraint PK_TABLEB primary key (Col1, Col2))
GO
Alter table TableB Add constraint FK_TableB_To_TableA foreign key (Col1) references TableA (Col1)
GO
Begin Tran
Insert into TableA (Col1, Col2) values (1, ‘Test’)
Insert into TableA (Col1, Col2) values (2, ‘Test2’)
Insert into TableA (Col1, Col2) values (3, ‘Test3’)
Insert into TableA (Col1, Col2) values (4, ‘Test4’)
Insert into TableB (Col1, Col2, Col3) values (1, 2, ‘BTest’)
Commit tran

Now, suppose that there are two different sessions and the activity is listed in the following table:

Session 1

Session2

 

Begin Tran

Update TableA

   Set Col2 = ‘Test-A’

 Where Col1 = 2

Set Lock_Timeout 5000 –- 5 seconds

Begin Tran

Insert into TableB(col1, col2, col3)

 Values (2, 3, ‘TestB’)

 

Session 2 will hang and will time-out returning the 1222 (lock timeout error) error after 5 seconds.   What occurs behind the scenes is that internally, SQL Server fires off a select statement  on TableA when the insert is done on TableB because it has to validate the foreign key and because that record is locked in Session 1, it results into the classic writers blocking readers scenario but an implicit case.  Also, remember that for Session 2, you need to trap that error and rollback/commit that transaction in order to release the locks else the locks will remain – you can check that very easily using sp_lock or other locking troubleshooting scripts.

One way to work around this issue is to define the primary key on the parent table (TableA) as a non-clustered index.  Once you do that, the issue won’t occur since the look-up will occur against the non-clustered index and that won’t be in a locked state since the PK column is not being modified.

This situation becomes even worse when the foreign key columns are not indexed because internal look-ups to validate the foreign key can then lead to full scans.  Actually, un-indexed foreign keys affect inserts, updates as well as deletes.  It is always a very good idea to index your foreign keys with the exception of look-up tables (less number of records) since over there doing a scan would be much faster than maintaining the index.

 

Posted in SQL Server | 1 Comment »