El TecnoBaúl de Kiquenet

Kiquenet boring stories

Archive for the ‘DataAccess’ Category

Performance DataAccess Oracle and ODP.NET

Posted by kiquenet en 18 junio 2016

FetchSize (OracleDataReader)

A number of blogs and sites mention increasing FetchSize of OracleDataReader to improve performance when fetching big volumes of data (e.g. thousands of rows). There are some documented experiments with exact numbers on this like:

http://metekarar.blogspot.com/2013/04/performance-improvement-for-odpnet.html

Table had a little more than 155K rows and was size of 31 MB. Yet, it took more than 5 minutes to complete for data adapter to fill data table.

The cause of the problem was the number of round trips client need to accomplish to get all the rows from database. If you can reduce the number of round trips, you can increase the fetch size so that in each turn command object will fetch more rows from database. Here is how it’s impletemented:

using (OracleConnection conn = new OracleConnection())
{
     OracleCommand comm = new OracleCommand();
     comm.Connection = conn;
     comm.FetchSize = comm.FetchSize * 8;
     comm.CommandText = "select * from some_table";

     try
     {
          conn.Open();
          OracleDataAdapter adap = new OracleDataAdapter(comm);
          System.Data.DataTable dt = new System.Data.DataTable();
          adap.Fill(dt);
     }
     finally
     {
          conn.Close();
     }
}

Notice the line with blue font, fetch size of command object increased by 8 times its default which is 128 KB at ODP.Net 11.2.0.3. OracleDataReader also has the FetchSize property. By increasing fetch size, you increase the cache size in memory to fetch rows.

What we gained is up to 96% performance improvement. Here are some timings with different fetch sizes:

Fetch Size             Timing (MI:SS.FF3)
Default (128 KB)       05:20.290
Default x 8 (1 MB)     00:52.941
Default x 32 (4 MB)    00:26.008
Default x 64 (8 MB)    00:12.409

Reference: http://metekarar.blogspot.com.es/2013/04/performance-improvement-for-odpnet.html

Strangely, unless the connection pooling is explicitly disabled (e.g. in the connection string), the increase/decrease of FetchSize stops having any effect. When the pooling is disabled though, it’s clear that the FetchSize can improve the performance (the more records, the bigger the effect).

It turns out that this unexpected behavior is limited to the following conditions: 1) The SELECT statement is exactly the same 2) The pooling is ON 3) The self-tuning is ON

Only in those conditions the first time the FetchSize is set, it gets somehow cached by ODP.NET and attempts to change it don’t work.

References Mark Williams
http://www.oracle.com/technetwork/issue-archive/2006/06-jul/o46odp-097508.html

string with_pool =
  "User Id=hr;
   Password=hr;
   Data Source=oramag;
   Enlist=false;
   Pooling=true"

Connection Pooling Overview

Making specific connection pooling parameter recommendations is difficult, because workloads can vary greatly. One general recommendation is to have a sufficiently high minimum pool size. I have often seen connection pools drained to the minimum during a lull in activity. When activity picks up rapidly, the connection pool has to create connections quickly rather than use connections in the pool. In most cases, the application server reduces the number of connections and remains relatively idle. Thus, there is no reason to have a low minimum pool size.

Controlling Fetch Size

Retrieving data from the database tier to the middle tier (or client tier) is one of the most expensive operations with respect to performance. If the end user consumes a lot of data, you will want your application to minimize the number of round-trip data fetches.

By default, ODP.NET will read 64KB of data from a result set at a time. You change this value by setting the FetchSize attribute for an OracleDataReader object. However, rather than arbitrarily setting the fetch size, ODP.NET provides the ability to control the number of rows that are fetched from a result set per round trip. The two properties you use to do this are RowSize and FetchSize.

RowSize is a property of the OracleCommand object, and the value for this property is assigned by ODP.NET when the statement associated with the command object is executed. You explicitly set the value of the FetchSize property. For example, if you want to retrieve 100 rows at a time from the result set per round trip, you set the FetchSize property:

dr.FetchSize = cmd.RowSize * 100;

if you are tempted to fetch only a single row at a time to "save resources and increase performance" (I have sometimes heard this advice), you will actually be dramatically reducing, rather than increasing, runtime performance. In addition, for this set of data, there is not much performance increase when fetching more than 1,000 rows at a time. Of course, this is also a narrow data set (with only two small columns). Apply this technique to a result set representative of your environment to determine optimal values for setting your fetch size.

Of course, there is a cost if the fetch size is arbitrarily large. More client-side memory and processor cycles will be needed to store and manage a larger amount of data. The goal is to find a high-performing balance between the number of round trips and the amount of data retrieved per trip.

Statement Caching

You enable the statement caching feature by setting the Statement Cache Size connection string parameter. The default value of this parameter is 0, meaning that statement caching is disabled by default. You set this parameter to the number of statements you plan to keep cached during your application’s lifetime. For example, if you plan to cache 16 statements, you will set this value to 16. ODP.NET will then cache the 16 most recently used statements. The 17th most recently used statement is aged out of the cache by the last 16 unique statements.

With statement caching enabled, ODP.NET will automatically cache any statement you execute. If you have enabled statement caching and you do not want to place a statement in the cache, set the AddToStatementCache property on OracleCommand to false:

string constr =
  "User Id=hr;
   Password=hr;
   Data Source=oramag;
   Enlist=false;
   Pooling=true;
   Statement Cache Size=1";


http://oradim.blogspot.com.es/2007/05/odpnet-tip-ref-cursors-and-fetchsize.html

create or replace procedure get_sales (p_refcur out sys_refcursor) is

begin

  — open the cursor using the passed in ref cursor
 
— sys_refcursor is a built in type

  open p_refcur for   select  *   from    sales;

end;

 // connection string — be sure to adjust for your environment
public const string constr = "User Id=sh; Password=sh; Data Source=otndemo; Pooling=false; Enlist=false";

      // enable extended sql tracing
      // this can be used to verify the number of rows fetched
      // uncomment to create trace file in user_dump_dest directory
      // EnableExtendedTrace(con);

static void EnableExtendedTrace(OracleConnection con)
{
    // create, setup, and execute command to enable extended sql trace
    OracleCommand cmd = con.CreateCommand();

    cmd.CommandText = "alter session set events ‘10046 trace name context forever, level 4’";
    cmd.ExecuteNonQuery();

    // clean up
    cmd.Dispose();
}

      // do the fetch test
      // passing 0 for "rows" will use default fetch size of 64k
      FetchTest(con, 100);
      FetchTest(con, 1000);
      FetchTest(con, 10000);
      FetchTest(con, 0);

static void FetchTest(OracleConnection con, int rows)

    {

      // used to track execution duration
      DateTime timeStart;
      DateTime timeEnd;
      double totalSeconds;

      // create and setup command to call stored procedure
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "get_sales";
      cmd.CommandType = CommandType.StoredProcedure;

      // create the ref cursor parameter and add to command parameter collection
      OracleParameter prm = new OracleParameter("p_refcur", OracleDbType.RefCursor, ParameterDirection.Output);
      cmd.Parameters.Add(prm);

      // get the data reader
      OracleDataReader dr = cmd.ExecuteReader();

      // There are 3 steps in getting the m_rowSize property value…
      // Step 1 – get the data reader type
      Type type = dr.GetType();

      // Step 2 – get the "m_rowSize" field info
      FieldInfo fi = type.GetField("m_rowSize", BindingFlags.Instance | BindingFlags.NonPublic);

      // Step 3 – get the value of m_rowSize for the dr object
      int rowSize = (int)fi.GetValue(dr);

      // if rows is greater than 0 use to set fetch size,
      // otherwise use default size (64k)

      if (rows > 0)
      {
        // set the fetch size on the dr object using the row size
        dr.FetchSize = rowSize * rows;
      }
      // capture test start time
      timeStart = DateTime.Now;

      // simply loop forcing the entire result set to be fetched
      while (dr.Read())      {      }

      // capture test end time
      timeEnd = DateTime.Now;

      // calculate total seconds for this test
      totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;

      // display time used for test
      Console.WriteLine("Number of rows = {0,5}: {1,8:###.0000} total seconds, fetch size = {2,9:###,###,###} bytes.", rows, totalSeconds, dr.FetchSize);

      // clean up
      dr.Dispose();
      prm.Dispose();
      cmd.Dispose();

    }

 

From Alex Keh (Oracle)
This video describes ODP.NET best practices for performance:
https://www.youtube.com/watch?v=ozMPGmsKcoA

The slides are here:
http://www.oracle.com/technetwork/topics/dotnet/tech-info/otn-vts-oracle-dotnet-presentations-2606459.zip

The code samples for many of the performance patterns are available in the following tutorials:
https://apexapps.oracle.com/pls/apex/f?p=44785:24:0::NO::P24_CONTENT_ID,P24_PREV_PAGE:10117,1

Books
Oracle SQL High Performance tuning
Oracle Performance Survival Guide
Troubleshooting Oracle Performance

http://guyharrison.squarespace.com/blog/2009/10/19/oracle-performance-programming-net.html
http://guyharrison.squarespace.com/blog/2008/1/28/accessing-oracle-from-powershell.html

Bind variables

Array Select

Array insert

 

http://grow-n-shine.blogspot.com.es/2011/05/optimize-net-access-with-oracle.html

Connection Pool (CP)

    Ensure you have enough connections in CP – better to have many than too few.
    OS-authenticated CP available with ODP.NET 11g
    Keep a steady state of CP – never destroy or create large number of connections
    Close/Dispose connections explicitly – don’t rely on garbage collector
    You can monitor CP performance counters using ODP.NET 11.1.0.6.20 and higher

Bind Variables

    Always use Bind Variables. Using Bind Variables will prevent reparsing of frequently executed statements. Literal value changes in commands force a reparse. Reparsing is fairly expensive CPU operation and requires shared pool locks.

Statement Caching

    Using statement caching retains parsed statement in shared pool.
    Cursor stays open on client side and Metadata remains on client
    Best used/works with Bind Variables
    Caching works with 10.2.0.2.20 and caches the last 10  executed statements.
    Developer can choose which statement to cache.

Statement Cache Size=0 (no caching)

Statement Cache Size=1 (caching)

With ODP.NET 11.1.0.7.20 cache size dynamically changes at runtime and provides automatic optimization/self-tuning. Self-tuning is enabled by default and no code changes are required.

 

Data Retrieval

You can control how much data is retrieved from the database per round-trip. Too much data can cause excessive client-side memory used and too little may cause additional round-trips. You can use OracleCommand.RowSize and OracleDataReader.FetchSize to control the result. FetchSize can be set as multiple of RowSize and RowSize can be dynamicall populated after statement execution.

FetchSize = RowSize X 1
FetchSize = RowSize X 100

Mass Data Movement with Arrays

    PL/SQL associative arrays can be used to pass large amounts of data between .NET and DB of the same data type.
    If you are executing the same statement multiple times, you could use a parameter array binding.

Statement Batching

    You can execute multiple commands in one DB round-trip using OracleDataAdapter.UpdateBatchSize.
    You can use anonymous PL/SQL for disparate or similar statements

ODP.NET DataTypes

    Try and avoid unnecessary datatype conversions between .NET and the DB
    Use OracleParameter.Dbtype

    public void CreateOracleDbParameter()
     {
        OracleParameter parameter = new OracleParameter();
        parameter.ParameterName = "pDName";
        parameter.DbType = DbType.String;
        parameter.Value = "ENGINEERING";
        parameter.SourceColumn = "DName";
     }

Use REF Cursors

With REF Cursors, you can retrieve data as needed and control data retrieved via FetchSize. You can fill a DataSet with just a portion of the REF cursor result. You can pass REF cursors back as input stored procedure parameters. Use OracleRefCursor class.

Oracle Performance Tuning in Visual Studio

    You can tune ad-hoc SQLs in query window

    Tune bad SQL using Oracle Performance Analyzer – requires:

        SYSDBA privilege

        database license for Oracle Diagnostic Pack

        database license for Oracle Tuning Pack

AWR and ADDM

These are built into Oracle Database 10g and are invaluable for diagnosing Performance issues.

AWR – Automatic Workload Repository

    Evolution of statspack

    builtin repository

    captures performance stats at regular intervals

ADDM – Automatic Database Diagnostic Monitor

    Analyses AWR stats and generates recommendations

AWR and ADDM nodes are now available in Visual Studio.

 

 

http://prasadaknair.blogspot.com.es/2012/01/odpnet-ref-cursors-and-fetch-size.html

using(OracleConnection conn = new OracleConnection(@"DataSource=source;User Id=username;Password=password;"))
{
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "StoredProcedureName";
        cmd.Parameters.Add("REF_CURSOR_SAMPLE",OracleType.RefCursor).Direction =  ParameterDirection.Output;
        conn.Open();
        OracleDataReader reader = cmd.ExecuteReader();
        FieldInfo fieldInfo = reader.GetType().GetField("m_rowSize", BindingFlags.Instance | BindingFlags.NonPublic);
        int cmdRowSize = (int)fieldInfo.GetValue(reader);
        reader.FetchSize = cmdRowSize * noOfRowsExpected;
        while(reader.Read())
        {
             // do your logic here
        }
        conn.Close();
}

Troubleshooting Oracle Performance
https://books.google.es/books?id=-cjAAwAAQBAJ&pg=PA649&lpg=PA649&dq=FetchSize++odp.net&source=bl&ots=ydjHcZbtSd&sig=3Sq84GfrsUQIteQTQFArdFvH5K4&hl=es&sa=X&ved=0ahUKEwj89pbAza_NAhVCbhQKHQUtBfY4ChDoAQg0MAM#v=onepage&q=FetchSize%20%20odp.net&f=false

ODP.NET. The default fetch size of ODP.NET (65,536) is defined in bytes, not rows. You can change this value through the FetchSize property provided by the  OracleCommand and OracleDataReader classes. The following code snippet is an example of how to set the value for fetching 100 rows.

Notice how the RowSize property of the OracleCommand class is used to compute the amount of memory needed to store the 100 rows. The C# program in the RowPrefetching.cs file provides a complete example:

sql = "SELECT id, pad FROM t";
command = new OracleCommand(sql, connection);
reader = command.ExecuteReader();
reader.FetchSize = command.RowSize * 100;
while (reader.Read())
{
    id = reader.GetDecimal(0);
    pad = reader.GetString(1);
    // process data
}
reader.Close();

As of ODP.NET version 10.2.0.3,  you can also change the default fetch size through the following registry entry (<Assembly_Version> is the full version number of Oracle.DataAccess.dll)

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\<Assembly_Version>\FetchSize

 

https://markhoxey.wordpress.com/2014/11/04/plsql-functions-and-cursor-fetch-size

  pkg_emp.get_emps (l_emp_cur);
  l_cur_no := DBMS_SQL.TO_CURSOR_NUMBER (l_emp_cur);
 
  DBMS_SQL.DESCRIBE_COLUMNS(l_cur_no, l_col_cnt, l_rec_tab);

  FOR i IN 1..l_col_cnt
  LOOP
     DBMS_OUTPUT.PUT_LINE(‘Column ‘ || l_rec_tab(i).col_name || ‘ max length of ‘ || TO_CHAR(l_rec_tab(i).col_max_len));
  END LOOP;
 
  DBMS_SQL.CLOSE_CURSOR(l_cur_no);

 

https://dbaportal.eu/2013/02/22/true-xcopy-runtime-for-oracle-odp-net-application/

   <oracle.dataaccess.client>
    <settings>
      <add name="DllPath" value="N:\ORACLE\ORA11odacR5"/>
      <add name="FetchSize"             value="65536"/>
      <add name="PromotableTransaction" value="promotable"/>
      <add name="StatementCacheSize"    value="0"/>
      <add name="TraceFileName"         value="%TEMP%\odpnet.trc"/>
      <add name="TraceLevel"            value="0"/>
      <add name="TraceOption"           value="0"/>
    </settings>
  </oracle.dataaccess.client>
  
  <!– Here we’re telling .NET framework two things:
       – which version of Oracle.DataAccess.dll we want to be used
       – and from where exactly it should load the assembly.
       Any version of Oracle.DataAccess.dll between 0.0.0.0 and 4.112.3.0
       will be replaced by 4.112.3.0.
       Note that publicKeyToken is "hash" dedicated to Oracle Corp. but might
       change in the future. We checked the token against GAC.
  –>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <publisherPolicy apply="no" />
        <assemblyIdentity name="Oracle.DataAccess"
                          publicKeyToken="89B483F429C47342"
                          culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-4.112.3.0"
                         newVersion="4.112.3.0"/>
        <codeBase
                version="4.112.3.0"
                href="file:///N:\ORACLE\ORA11odacR5\odp.net4\odp.net\bin\4\Oracle.DataAccess.dll"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>

:: RunOraDUAL.bat
:: Oracle11g [11.2.0.3] Instant Client with ODAC 11.2.0.3.20
::
:: Batch routine to launch OraDUAL.exe from newtork drive.
::
:: ———————————————————
 
title Oracle11g Instant Client – ODAC 11.2.0.3.20
SET NLS_LANG=SLOVENIAN_SLOVENIA.EE8MSWIN1250
SET NLS_DATE_FORMAT=DD.MM.YYYY
SET ORACLE_HOME=N:\ORACLE\ORA11ODACR5
SET TNS_ADMIN=N:\ORACLE\ORA11ODACR5
 
SET PATH=%ORACLE_HOME%;%ORACLE_HOME%\ODP.NET4\BIN;%ORACLE_HOME%\odp.net4\odp.net\bin\4;%PATH%;
 
start OraDUAL.exe
:: End

Posted in .NET, DataAccess, Oracle, Performance | Etiquetado: , , , | 2 Comments »

Introducing SQLite

Posted by kiquenet en 22 agosto 2014

http://damienbod.wordpress.com/2013/11/14/using-sqlite-with-net/

Helper class:

https://github.com/iancooper/Paramore/blob/master/Renegade/UserGroupManagement.Configuration/DomainDatabaseBootStrapper.cs
csharp-sqlite SQLiteClientTestDriver.cs

Getting started with SQLite and .NET
http://blog.kurtschindler.net/getting-started-with-sqlite-and-net/

You can install and use the core SQLite library on the official download page, but as a .NET developer your best bet is go with System.Data.SQLite – an ADO.NET provider for the SQLite engine. It also includes design-time support in Visual Studio 2005/2008!

See using-SQlite with Entity Framework 6 and the Repository Pattern

http://damienbod.wordpress.com/2013/11/18/using-sqlite-with-entity-framework-6-and-the-repository-pattern/

Code: https://github.com/damienbod/SQLiteExamples

What is it? (Taken from http://schimpf.es/sqlite-vs-mysql )
SQLite is a single-file based database which is useful for testing and for embedding in applications. This means that all the information is stored in a single file on a file system and you use a SQLite library to open this file to read and write your data.

Here’s 3 links to the SQLite website which are worth reading:

Hello World Project (Taken from http://blog.tigrangasparian.com/2012/02/09/getting-started-with-sqlite-in-c-part-one/)

Using Entity Framework 6 with SQLite (taken from brice-lambson.blogspot.ch )

This an example doesn’t work quiet as easy as described. To use Entity Framework 6 with SQLite, the source code of the SQLite package needs to be changed, or you must get the pre-release package fromhttps://www.myget.org/F/bricelam/

SQLite Administration
Use the Firefox addon: https://addons.mozilla.org/de/firefox/addon/sqlite-manager/

Here you can browser select, insert as you wish. It is easy to use and uncomplicated.

SQLite Manager is a Firefox addon (or see project hosted on google code:http://code.google.com/p/sqlite-manager/)

SQLite Manager GUI

sqliteDb1

 

SQlite Administrator

SQLite Administrator is a great little freeware tool supporting multiple languages and many features.

SQLite Administrator GUI

Link for SQL as understood by SQLite: http://www.sqlite.org/lang.html

sqlite-net available from this link https://github.com/praeclarum/sqlite-net.

Unfortunately, the getting started documentation are not enough. It doesnt even mention how to create a database. I tried looking at the examples, unfortunately, the examples are broken(unable to compile, run time error etc).

The most practical tutorial i can find on the net is http://blog.tigrangasparian.com/2012/02/09/getting-started-with-sqlite-in-c-part-one/

Unfortunately, sqlite-net doesnt fully support sqlite.org sqlite implementation, thus making the tutorial useless for praeclarum sqlite-net.

Error :- Mixed mode assembly is built against version ‘v2.0.50727’ of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information
http://geeksprogrammings.blogspot.com.es/2014/08/sqlite-error-mixed-mode-assembly-.html

Discusión en ALT.NET Hispano
https://groups.google.com/forum/#!msg/altnet-hispano/A8-mXfuifnk/TfcT1VLuYj8J

References:
http://stackoverflow.com/questions/19851213/how-to-usecreate-db-create-table-query-etc-praeclarum-sqlite-net


http://system.data.sqlite.org

https://addons.mozilla.org/de/firefox/addon/sqlite-manager/

http://sqliteadmin.orbmu2k.de/

http://blog.tigrangasparian.com/2012/02/09/getting-started-with-sqlite-in-c-part-one/

http://brice-lambson.blogspot.ch/2012/10/entity-framework-on-sqlite.html

http://brice-lambson.blogspot.ch/2013/06/systemdatasqlite-on-entity-framework-6.html

http://www.connectionstrings.com/sqlite/

http://www.codeproject.com/Articles/236918/Using-SQLite-embedded-database-with-entity-framewo

http://stackoverflow.com/questions/2514785/how-to-create-an-entity-framework-model-from-an-existing-sqlite-database-in-visu

http://www.thomasbelser.net/2009/01/25/c-sharp-und-sqlite-eine-kleine-einfuhrung/

http://cplus.about.com/od/howtodothingsinc/ss/How-To-Use-Sqlite-From-Csharp.htm

http://stackoverflow.com/questions/11591002/how-can-i-use-sqlite-in-a-c-sharp-project

http://schimpf.es/sqlite-vs-mysql/

http://chinookdatabase.codeplex.com/

http://stackoverflow.com/questions/14510096/entity-framework-6-sqlite

http://blogs.msdn.com/b/mim/archive/2013/06/18/sync-framework-with-sqlite-for-windows-store-apps-winrt-and-windows-phone-8.aspx

http://code.msdn.microsoft.com/windowsapps/Sqlite-For-Windows-8-Metro-2ec7a882

http://blogs.msdn.com/b/andy_wigley/archive/2013/06/06/sqlite-winrt-database-programming-on-windows-phone-and-windows-8.aspx

Posted in .NET, DataAccess | Etiquetado: | Leave a Comment »

El maravilloso mundo de las transacciones III: DTC

Posted by kiquenet en 5 enero 2011

La relación entre DTC (MSDTC service. Coordinador de Transacciones Distribuidas) y las transacciones (TransactionScope en C#).

Existen los comandos: sc y netsvc.

Ruta de registro: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSDTC

Comandos para iniciar:

sc config msdtc start= auto
sc start msdtc

Jesús López posted on Foros MSDN

Cuando dentro de un TransactionScope se abre más de una conexión, aunque
sean a la misma base de datos, la transacción se promociona a transacción
distribuida. Las transacciones distribuidas son maneejadas por el MSDTC
(Microsoft Distributed Transaction Coordinator). De forma predeterminada el
acceso de red de MSDTC está deshabilidado.  Si el cliente que ejecuta la
transacción distribuida no está en la misma máquina que SQL Server, entonces
es necesario acceso de red de MSDTC.

Vamos que el mensaje de error te está diciendo exactamente qué es lo que
está pasando y además te está diciendo como arreglarlo. En el servidor SQL,
ejecutas la herramienta administrativa de Servicios de Componentes, todos
los programas -> herramientas administrativas -> servicios de componentes, o
panel de control->herramientas administrativas->servicios de componentes o
C:\WINDOWS\system32\Com\comexp.msc. Expandes Raíz de la Consola->Servicios
de Componentes->Equipos. Haces click con el botón derecho en Mi PC, eliges
propiedades, te vas a la ficha MSDTC, pulsas el botón "Configuración de
Seguridad" y allí habilitas:

1) Acceso a DTC desde la red
a) permitir clientes remotos
b) permitir entrantes
c) permitir salientes

http://support.microsoft.com/kb/166819/en-us?fr=1

C:\>sc \\REMOTE1 config Schedule start= disabled
[SC] ChangeServiceConfig SUCCESS

C:\>netsvc /start \\REMOTE1 "Schedule"
Error code 1058

C:\net helpmsg 1058

The specified service is disabled and cannot be started.
C:\>sc \\REMOTE1 config Schedule start= demand [SC] ChangeServiceConfig SUCCESS

C:\>netsvc /start \\REMOTE1 "Schedule" Service is pending start on \\REMOTE1

C:\>sc \\REMOTE1 qc Schedule [SC] GetServiceConfig SUCCESS

Referencias útiles sobre el tema: DTC y distintos problemas.

http://www.thereforesystems.com/turn-on-msdtc-windows-7/

http://stackoverflow.com/questions/1764165/understanding-msdtc-in-windows

Troubleshooting Problems with MSDTC
http://msdn.microsoft.com/en-us/library/aa561924.aspx

http://stackoverflow.com/questions/1690892/transactionscope-automatically-escalating-to-msdtc-on-some-machines

How to prevent automatic MSDTC promotion
http://softwaredevelopmentsolutions.blogspot.com/2010/08/how-to-prevent-automatic-msdtc.html

Posted in .NET, DataAccess | Etiquetado: , | Leave a Comment »

Transacciones: Interbloqueos en SQL Server

Posted by kiquenet en 18 septiembre 2010

Por si fuera poco lo que hemos visto hasta ahora, aún tenemos pendiente revisar la gestión de interbloqueos que realiza SQL Server. Este punto es de vital importancia en nuestras aplicaciones que acceden concurrentemente a los datos en nuestra base de datos.

Un interbloqueo es una situación en la que dos transacciones están esperando para utilizar un recurso bloqueado.

SQL Server detecta automáticamente una situación de interbloqueo mediante un método denominado detección de cadenas circulares de bloqueos. Cuando un interbloqueo ocurre, SQL Server termina el proceso que haya generado la cadena circular de bloqueos.

Los interbloqueos no se producen únicamente a nivel de página de datos, de hecho, muchos interbloqueos se producen a nivel de página de índice.

Minimización de la contienda de bloqueo

Para minimizar el número de interbloqueos se deben considerar las siguientes recomendaciones:

  • Las transacciones deben ser cortas y afectar al menor número de datos posibles.
  • Se debe dar preferencia a la utilización de alguna de las técnicas del enfoque optimista.
  • Puede resultar conveniente establecer una prioridad baja (“set deadlockpriority low”) para las consultas DSS y una alta (“set deadlockpriority normal”) para las consultas OLTP.
  • El orden de acceso a las tablas debe ser siempre el mismo en las distintas partes del código.
  • Se debe minimizar el uso de “holdlock”. Por ejemplo, el código:
declare @SEQNO int   begin transaction
   SELECT @SEQNO = isnull(SEQ#,0)+1
   FROM SEQUENCE WITH holdlock
   /*
      En ausencia de holdlock el bloqueo compartido sería liberado
      por lo que si otra transacción concurrente ejecutara el mismo
      comando obtendría el mismo número de secuencia
   */
   UPDATE SEQUENCE
   SET SEQ# = @SEQNO
   /*
      Ahora puede hacerse lo que se desee con este número de
      secuencia
   */
   …
commit transaction
podría reescribirse como:
declare @SEQNO int   begin transaction
   UPDATE SEQUENCE
   SET @SEQNO = isnull(SEQ#,0)+1
   FROM SEQUENCE
   SELECT @SEQNO
   …
commit transaction

De esta forma se obtiene directamente el bloqueo de actualización eliminándose la posibilidad de que se produzca un interbloqueo.

  • SQL Server proporciona un soporte completo para establecer la granularidad del bloqueo a nivel de fila. Una granuralidad del bloqueo más fina proporciona una mejor concurrencia, minimizando el número de interbloqueos, aunque aumenta el trabajo del servidor para gestionar los bloqueos.
  • Se debe evitar las interactuaciones con el usuario dentro de una transacción con el fin de minimizar el tiempo de duración de la misma.
  • Se debe seleccionar cuidadosamente el nivel de aislamiento de una transacción, ya que el nivel de aislamiento determina el tipo de bloqueo. Es posible reducir el número de interbloqueos bajando el nivel de aislamiento.

Gestión de interbloqueos

SQL Server devuelve el error 1205 al cliente cuando termina un proceso como resultado de un interbloqueo.


Del Sr. Profesor Alcalde

Posted in .NET, DataAccess | Etiquetado: | Leave a Comment »

Transacciones: Enfoques de control de transacciones en SQL Server

Posted by kiquenet en 18 septiembre 2010

Siempre con el objetivo de maximizar el rendimiento de nuestras aplicaciones multiusuario que trabajan accediendo a bases de datos, hay una cosa más que debemos tener en cuenta: los enfoques de control de transacciones.

Al implementar aplicaciones multiusuario que trabajan contra bases de datos se pueden adoptar dos enfoques para el control de transacciones: un enfoque de bloqueo optimista y un enfoque de bloqueo pesimista.

El bloqueo optimista supone que no se va a hacer nada en el código de la aplicación que imponga explícitamente bloqueos de los recursos cuando se esté trabajando con ellos. En lugar de hacer esto, se confía en que el gestor de bases de datos (Microsoft SQL Server, por ejemplo) se encargue de hacerlo mientras el programador únicamente se debe centrar en la lógica de la aplicación.

El bloqueo pesimista supone que el código de la aplicación intentará imponer algún tipo de mecanismo de bloqueo al gestor de bases de datos.

Cada uno de estos enfoques presentan una serie de ventajas e inconvenientes que iremos analizando y además presentaremos algunas técnicas para implementarlos.

Enfoque optimista de gestión de transacciones en SQL Server

La eficiencia de una aplicación multiusuario (usuarios concurrentes de acceso a bases de datos, en nuestro caso) que trabaje contra SQL Server se ve condicionada por ciertos factores entre los que se encuentran el enfoque de control de transacciones que adoptemos.

Como se introducía en el post anterior de esta saga “Enfoques de control de transacciones en SQL Server“,existen dos enfoques básicos: enfoque optimista y enfoque pesimista. Del enfoque que seleccionemos y de lo hábiles que seamos observando ciertas sencillas reglas en cada uno de ellos dependerá la eficiencia de nuestra aplicación.

Un bloqueo optimista supone que no se va a hacer nada en el código de la aplicación que imponga explícitamente bloqueos de los recursos cuando se esté trabajando con ellos. En lugar de hacer esto, se confía en que el gestor de bases de datos (Microsoft SQL Server, por ejemplo) se encargue de hacerlo mientras el programador únicamente se debe centrar en la lógica de la aplicación.

Una vez hemos decidido que vamos a dejar que SQL Server gestione las transacciones por nosotros, y pese a que hemos descargado sobre el gestor de bases de datos en gran medida la responsabilidad de hacer que nuestra aplicación sea eficiente, aún hay ciertas cosas que como buenos programadores debemos tener en cuenta.

Para implementar un bloqueo optimista en una aplicación sin que ésta se detenga bruscamente cuando exista un número excesivo de bloqueos en el servidor es preciso observar ciertas reglas sencillas:

  • Se debe minimizar el tiempo de duración de una transacción.
  • El código de la aplicación debe asegurar que las actualizaciones se realicen sobre un registro concreto, en lugar de mantener el bloqueo mientras el usuario está examinado los datos.
  • Asegurar que todos los códigos de la aplicación actualizan y seleccionan tablas en el mismo orden. Esto evitará que se produzcan los bloqueos permanentes.

Bloqueo optimista utilizando “timestamp”

SQL Server proporciona un tipo especial de datos denominado “timestamp”. El valor de una columna de este tipo es generado automáticamente cada vez que se almacena una fila con “insert” o “update”. Es simplemente un contador que cambia de manera monótona con cada actualización o inserción.
El propósito, para nuestro caso en particular, del tipo de datos “timestamp” consiste en servir como número de versión para los esquemas de bloqueo optimista.
Para poder emplear un bloqueo optimista basado en este tipo de datos, deben cumplirse las siguientes dos condiciones:

  • La tabla debe tener una clave primaria.
  • La tabla debe tener una columna de tipo “timestamp”.

El cliente lee la fila con el valor actual de la columna de marca temporal, pero no mantiene ningún bloqueo. En algún momento posterior, cuando el cliente quiere actualizar la fila, debe asegurarse de que ningún otro cliente haya cambiado la misma fila mientras tanto (puesto que no hay bloqueos, es responsabilidad del cliente el asegurarse de que los cambios hechos por otros clientes sean preservados). El cliente prepara de una forma especial el “update”, utilizando una columna “timestamp” como marcador de versión:

UPDATE TABLA
SET COLUMNA_CAMBIADA = NUEVO_VALOR
WHERE COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA AND
      timestamp = NUM_VERSION_OLD

Puesto que la cláusula “where” incluye la clave primaria, sólo se verá afectada una fila como máximo. Si algún otro cliente ha cambiado la fila, la segunda parte del “where” fallará, dándose como resultado que el “update” ha actualizado cero filas, como indicación de fallo de bloqueo. El cliente puede entonces elegir entre volver a leer los datos o efectuar cualquier otro procedimiento de recuperación que se considere oportuno.

Existen varias ineficiencias asociadas a esta forma de trabajo que se expondrán en el siguiente apartado. Por ello, si se va a utilizar un enfoque optimista basado en una marca de tiempo, se recomienda que dicha marca esté basada en un tipo de datos “int”.

Bloqueo optimista utilizando “int”

La única diferencia con respecto al método anterior es que, puesto que el servidor no va a actualizar de forma automática el valor de esta columna, será el cliente el encargado de hacerlo.

La sentencia “update” tendrá la forma:

UPDATE TABLA
SET COLUMNA_CAMBIADA = NUEVO_VALOR,
    NUM_VERSION = NUM_VERSION_OLD+1
WHERE COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA AND
      NUM_VERSION = NUM_VERSION_OLD

Las diferencias con respecto al método anterior son las siguientes:

  • El tipo de datos “int” ocupa sólo cuatro bytes mientras que el tipo de datos “timestamp” ocupa ocho.
  • Al utilizar el tipo de datos “int” el cliente debe añadir la actualización de la marca en las sentencias “insert” y “update”, además de incluir el tratamiento en la cláusula “where”.
  • Si se usa el tipo de datos “timestamp”, después de hacer un “update” será necesario que el cliente realice un nuevo “select” para obtener la nueva marca de tiempo, si es que quiere continuar trabajando con los mismos datos.
UPDATE TABLA
SET COLUMNA_CAMBIADA = NUEVO_VALOR
WHERE COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA AND
     timestamp = NUM_VERSION_OLD   /*
Si se quiere seguir trabajando con la misma fila, será necesario
conocer el nuevo valor de la columna timestamp ya que ha
sido asignado automáticamente por el servidor
*/   SELECT timestamp
FROM TABLA
WHERE COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA

En el caso de usar una marca basada en un tipo de datos “int”, el cliente sabe después del “update” el nuevo valor de la marca, por lo que no tendrá que realizar una consulta para seguir trabajando con los mismos datos.

UPDATE TABLA
SET COLUMNA_CAMBIADA = NUEVO_VALOR,
    NUM_VERSION = NUM_VERSION_OLD+1
WHERE COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA AND
      NUM_VERSION = NUM_VERSION_OLD   /*
Aquí el cliente sabe cuál es el nuevo valor de la columna
NUM_VERSION, que es el que tenía almacenado + 1
*/

En la mayoría de los casos, la elección de este enfoque de control de transacciones es acertada. Sin embargo, existen ciertas situaciones donde el número de usuarios que acceden concurrentemente a la base de datos es elevado, las transacciones son largas y un largo etcétera en los que será conveniente evaluar la idoneidad de apostar por un enfoque pesimista de gestión de transacciones.

Enfoque pesimista de gestión de transacciones en SQL Server

Diseñar nuestra aplicación, que va a soportar el acceso concurrente a datos, basándonos en un enfoque optimista de gestión de transacciones implica suponer que SQL Server va a administrar a la perfección las contiendas de bloqueos que se produzcan cuando nuestra aplicación esté en marcha.

Por un lado, resulta poco realista suponer que un programador o un usuario no pueda hacer nada que provoque explícitamente un bloqueo y, por el otro, tenemos aplicaciones cuyas exigencias nos obligan a determinar de forma explícita con qué lógica deben ser manejadas las acciones que realizamos sobre los datos… realmente existen muchas situaciones que dan lugar a que se produzcan un gran número de bloqueos en el servidor, lo que puede llegar a inutilizar nuestra aplicación si no ponemos cuidado a la hora de diseñar el acceso a la base de datos.

Un enfoque pesimista de gestión de transacciones consiste en diseñar de qué forma vamos a condicionar a nuestro sistema gestor de bases de datos SQL Server para que gestione la competición por el acceso a datos o contienda de bloqueo.

En SQL Server tenemos dos posibilidades, no excluyentes, de tomar el control, o al menos de condicionar el comportamiento, sobre cómo se va a manejar la contienda de bloqueo:

  • utilizando los niveles de aislamiento de transacciones que implementa SQL Server
  • utilizando sugerencias para nivel de aislamiento y granularidad de bloqueo

Utilización de los niveles de aislamiento de las transacciones

Los niveles de aislamiento determinan el grado en que los datos incoherentes de una transacción están protegidos frente a otras transacciones. En teoría, toda transacción debe estar completamente aislada de otras transacciones, pero en la realidad, por razones prácticas, esto puede no ser cierto siempre. El uso de un nivel u otro estará condicionado por la naturaleza de los procesos de tratamiento de datos de cada aplicación en particular.

Para establecer el grado de aislamiento de las transacciones durante toda una conexión, en SQL Server se utiliza la sentencia “set transaction isolation level”.

En un entorno multiusuario, pueden darse cuatro situaciones: actualización perdida, lecturas sucias, lecturas no repetibles, lecturas fantasma.

  • Actualización perdida: No proporciona ningún aislamiento. La última transacción en modificar el conjunto de datos prevalecerá y los cambios realizados por otras transacciones se perderán.
  • Lecturas sucias: Una transacción puede leer datos que están siendo modificados por otras transacciones. Estos datos son incoherentes porque las otras transacciones pueden decidir cancelar los cambios.
  • Lecturas no repetibles: En esta situación una transacción lee los mismos datos dos veces, pero entre una lectura y otra, otra transacción ha modificado los datos. Así, los datos obtenidos en la primera lectura serán distintos a los obtenidos en la segunda.
  • Lecturas fantasma: En esta situación, la transacción 1 lee los datos en un rango de claves del 1 al 8, existiendo las filas 1, 3 y 5. Una segunda transacción inserta en la misma tabla una fila con la clave 2. Cuando la transacción 1 realice una nueva consulta sobre dicho rango de claves se encontrará con una fila que antes no existía.

Idealmente, un SGBD debe proporcionar niveles de aislamiento para evitar todas estas situaciones. A veces, por razones de rendimiento y siempre condicionado por la naturaleza de los procesos que manejan la información, pueden permitirse algunas de estas situaciones.

ANSI define los siguientes cuatro estándares para el aislamiento de transacciones: lectura de datos no confirmados (N0), lectura de datos confirmados (N1), lectura repetible (N2) y serializable (N4). SQL Server proporciona soporte para todos estos niveles.

Lectura de datos no confirmados

Si se selecciona el modo de lecturas no confirmadas no se proporciona ningún aislamiento a las transacciones.

Una transacción que se ejecute con este nivel de aislamiento no será inmune a las lecturas sucias, lecturas no repetibles ni lecturas fantasma.

La instrucción T-SQL que selecciona este nivel de aislamiento es:

SET transaction isolation level READ uncommitted
Lectura de datos confirmados

Es el comportamiento predeterminado de SQL Server.

En este modo no se ejecutan inicios de transacciones implícitas. Durante la ejecución de una sentencia se establece un bloqueo de actualización sobre el recurso a modificar. En cuanto termine esta sentencia, se liberará el bloqueo.

En este nivel de aislamiento se protege a la transacción contra lecturas sucias, pero las transacciones no son inmunes a la pérdida de actualizaciones, las lecturas no repetibles ni a las lecturas fantasma.

SET transaction isolation level READ committed
Lectura repetible

SQL Server da inicio a una transacción de manera implícita al ejecutar una sentencia de modificación de datos, por lo que será necesaria una sentencia “commit” o “rollback” para terminarla. Este modo permite a las transacciones establecer bloqueos compartidos y exclusivos sobre los recursos a modificar.

Bajo este nivel de aislamiento, las transacciones están protegidas contra pérdida de atualizaciones, las lectruras sucias y las lecturas no repetibles. Sin embargo, dichas transacciones sí pueden sufrir problemas de lecturas fantasma.

SET transaction isolation level repeatable READ
Serializable. Serialización y bloqueo de rango de clave

Este modo es el más estricto, ofreciéndose aislamiento completo a las transacciones.

SQL Server da comienzo de manera implícita a una transacción, utilizando un bloqueo de rango de clave sobre las páginas índice, para asegurar aislamiento frente lecturas fantasma.

Bajo este nivel de aislamiento las transacciones utilizan bloqueos compartidos, exclusivos y de rango de clave para asegurar el aislamiento total frente a otras transacciones.

Si la operación es una búsqueda de rango, se establece un bloqueo de intención compartido sobre el rango de claves especificado en la cláusula “where”. Otras transacciones podrán leer ese rango, pero no podrán insertar ni borrar registros dentro del mismo.

Si la operación es de búsqueda o borrado de una fila no existente, SQL Server establece un bloqueo de intención compartido sobre la página de índice para el rango de claves comprendido en la sentencia de la operación.

Si la operación es de inserción, SQL Server establece un bloqueo de intención de inserción sobre la página de índice, sólo para la clave que está siendo insertada. Lo mismo ocurre si la operación es de borrado.

Dado que pueden llegar a bloquearse un elevado número de recursos, pueden existir problemas de baja concurrencia en las aplicaciones que establezcan este nivel de aislamiento.

SET transaction isolation level serializable

Utilización de sugerencias para niveles de aislamiento y granularidad de bloqueos

Como se ha comentado en el apartado anterior, se puede establecer el nivel de aislamiento de las transacciones que se ejecuten en una conexión con la sentencia “set transaction isolation level”.

Esta sentencia T-SQL crea un nivel de global de aislamiento para la conexión, lo que proporciona un nivel de aislamiento homogéneo para la aplicación. Sin embargo, a veces es recomendable utilizar distintos niveles de aislamiento dependiendo de la naturaleza del proceso de tratamiento de los datos en cuestión, etc.

SQL Server permite especificar sugerencias de tablas en las cláusulas “select”, “delete”, “update” e “insert”, lo que permite sustituir el nivel de aislamiento definido para la conexión. Esta sustitución del nivel de aislamiento estará vigente mientras dure la transacción.

Aunque suele ser buena idea que sea el optimizador el que tome las decisiones de qué granularidad de bloqueo y nivel de aislamiento se debe utilizar, a veces resulta útil sugerir al gestor a utilizar una granularidad y niveles de aislamiento determinados.

Sugerencias de nivel de aislamiento de una transacción
  • “holdlock”: Dentro de una transacción, los bloqueos compartidos (los que se establecen al seleccionar una fila, por ejemplo) se liberan en cuanto la sentencia que mantiene el bloqueo se termina. Para mantener el bloqueo compartido durante toda la transacción se utiliza la cláusula “holdlock”. Esta cláusula es útil, por ejemplo, para actualizar el valor de un contador no “identity”.
    declare @SEQNO int
    begin transaction
    SELECT @SEQNO = isnull(SEQ#,0)+1
    FROM SEQUENCE WITH holdlock
    /*
    En ausencia de holdlock el bloqueo compartido sería liberado
    por lo que si otra transacción concurrente ejecutara la
    misma sentencia obtendría el mismo número de secuencia
    */
    UPDATE SEQUENCE
    SET SEQ# = @SEQNO
    /*
    Ahora puede hacerse lo que se desee con este número
    de secuencia
    */
    …
    commit transaction

    Hay que resaltar una cuestión que puede llevar a engaño. La ejecución del conjunto de sentencias anterior, impide que dos procesos ejecutándose concurrentemente acaben obteniendo el mismo número de secuencia. Sin embargo, no evita que se produzca una situación de interbloqueo con la consecuente terminación del proceso que lo ha producido. Esta situación se daría en el caso de que los dos procesos ejecutaran el “select” y luego ambos quisieran ejecutar el “update”. Sólo uno de ellos lo conseguiría y SQL Server terminaría el proceso que ha provocado el interbloqueo. Reescribiendo el código de la siguiente manera se evitaría esta situación:

    declare @SEQNO int
    begin transaction
    UPDATE SEQUENCE
    SET @SEQNO = isnull(SEQ#,0)+1
    FROM SEQUENCE
    SELECT @SEQNO
    …
    commit transaction

    De esta forma se obtiene el bloqueo de actualización directamente y se evita la posibilidad de interbloqueo.

  • “nolock”: Puede usarse esta sugerencia para especificar que no se establezca ningún bloqueo compartido sobre el recurso y que se denieguen las solicitudes de bloqueos de actualización o exclusivos. Esta opción permitirá las lecturas sucias a otras transacciones. Es útil en los entornos de generación de informes donde los datos aproximados resulten aceptables.
  • “readcommitted”: Es equivalente a utilizar “set transaction isolation level” salvo, por supuesto, que sólo tiene efecto hasta que termine la transacción.
  • “readpast”: Esta sugerencia se aplica sólo a las sentencias “select” y permite que la consulta se salte filas sobre las que existan bloqueos. Dadas las restricciones a las que está sometida el uso de esta sugerencia, es prácticamente imposible implementarla en entornos OLTP.
  • “readuncommitted”: Equivalente a especificarlo con “set transaction isolation level”, salvo que sólo tiene efecto hasta que termine la transacción, y es igual a la sugerencia “nolock”.
  • “repeatableread”: Equivalente a usar “set transaction isolation level”, salvo que sólo tiene efecto hasta el fin de la transacción.
  • “serializable”: Equivalente a usar “set transaction isolation level”, salvo que sólo tiene efecto hasta que termine la transacción.
Sugerencias de granularidad de bloqueo

Quiero remarcar que no uso la palabra “sugerencia” por gusto: en SQL Server se definen así, como sugerencias, y es que el optimizador de consultas del gestor de base de datos a tenderá a nuestras sugerencias siempre y cuando no considere que perjudican al rendimiento del servidor.

  • “rowlock”: Fuerza al gestor de bloqueos a establecer un bloqueo a nivel de fila.
  • “paglock”: Fuerza al gestor a establecer un bloqueo a nivel de página.
  • “tablock”: Se usa para establecer un bloqueo a nivel de tabla.
  • “tablockx”: Especifica un bloqueo exclusivo sobre una tabla. Sólo es necesario si se pretenden realizar operaciones de mantenimiento de la tabla.
  • “updlock”: Esta opción es similar a “holdlock”. La diferencia es que “holdlock” establece un bloqueo compartido y “updlock” establece un bloqueo de actualización sobre los recursos mientras dure la transacción.

Enfoque mixto de gestión de transacciones en SQL Server

Cuando los requisitos de eficiencia de una aplicación en la que se produce acceso concurrente a datos sobre SQL Server son muy estrictos, no podemos basar el diseño de nuestra aplicación ni en un enfoque optimista de gestión de transacciones ni en un enfoque pesimista de gestión de transacciones ya que corremos el riesgo de no satisfacer dichos requisitos.

Además, aunque en apariencia estos dos enfoques de control de transacciones expuestos con anterioridad cubren las necesidades de cualquier aplicación, esto no es así dado que las técnicas empleadas para implementar uno y otro enfoque presentan ciertas carencias que obligan a diseñar otras formas de trabajo.

En cuanto al enfoque optimista, esas carencias se resumen en el siguiente punto:

  • La carencia principal reside en el hecho de que puede darse que dos usuarios seleccionen los mismos datos para su posterior modificación. Ambos usuarios podrán realizar todas las modificaciones que quieran, pero sólo uno de ellos conseguirá completar la operación con éxito, dado que el mecanismo implementado por estos enfoques optimistas impedirá que se pierdan modificaciones. Si el mantenimiento afectaba a un elevado volumen da datos, el usuario que no consiga completar la actualización de los datos habrá perdido mucho tiempo de trabajo.
  • Existe la necesidad de implementar la gestión de tratamiento de carreras de actualización de datos.

Las técnicas de enfoque optimista funcionan bien si el número de tablas a manejar es pequeño. Sin embargo, esta técnica complica el desarrollo de aquellos mantenimientos que manejen un elevado número de tablas. En estos casos debe estudiarse la posibilidad de utilizar un enfoque pesimista del control de transacciones, siempre y cuando esta decisión no genere nuevos problemas.

En cuanto al enfoque pesimista, las carencias se resumen en los siguientes puntos:

  • La elección en cada caso del nivel de aislamiento de una transacción o una conexión y de la granularidad del bloqueo es una tarea extremadamente complicada dado el elevado número de factores y combinaciones que pueden darse. Una elección no adecuada puede hacer que la eficiencia del servidor, y por tanto de la aplicación, se vea seriamente afectada.
  • Existe la necesidad de implementar el tratamiento de interbloqueos.
  • Por otro lado, existe el mismo problema que había con el enfoque optimista referente a la actualización simultánea de los mismos datos por procesos distintos, con la particularidad de que ahora el tratamiento de interbloqueos será diferente.
  • Finalmente, existe un problema relacionado con el carácter indefinido que tienen los bloqueos sobre los datos. Con un enfoque pesimista podría darse la situación de que un usuario bloqueara los datos para su posterior modificación y que, en el caso extremo, nunca liberara los recursos bloqueados. Esta situación que parece imposible es realmente usual.

Los siguientes esquemas de trabajo propuestos, basados en que cada una de las copias de nuestra aplicación respete una serie de reglas a la hora de acceder a los datos, tratan de cubrir estas carencias. Cabe resaltar que no son excluyentes con respecto a los métodos expuestos anteriormente, necesitándose en algunos casos dichos métodos para asegurar el correcto funcionamiento y mejorar la eficiencia de estos últimos.

Los dos métodos están basados en añadir una columna a las tablas que indique si la fila en cuestión está bloqueada o no. La elección de qué tablas son las que deben incluir esta columna depende de cada modelo de datos y de las tablas que se vean involucradas en cada mantenimiento.

Bloqueo pesimista utilizando “flag + plan de contingencia”

En las tablas en las que se quiera controlar el acceso concurrente se introducirá una columna “IT_EN_USO”. En una fila que haya sido seleccionada por un proceso de modificación, el valor de dicha columna será 1, y valdrá 0 en cualquier otro caso.

Un proceso que quiera modificar los datos de una fila, sólo podrá hacerlo si “IT_EN_USO” vale 0. Para ocupar el recurso, pondrá el valor 1 en dicha columna.

Si un mismo proceso necesita modificar varias filas, de la misma tabla o de varias, deberá obtener todos los recursos a modificar o no ocupar ninguno. En caso de no poder ocupar todos los recursos deberá liberar aquellos que ya haya ocupado y se mostrará un mensaje al usuario que le invite a intentarlo más tarde.

Tras finalizar la modificación de los datos, el proceso que los ha modificado será el encargado de liberar los recursos poniendo el valor 0 en la columna “IT_EN_USO” en aquellas filas que corresponda.

Dentro de la utilización de este enfoque, pueden darse mantenimientos que necesiten la interacción con el usuario (para la recogida de los nuevos datos) y mantenimientos que no la necesiten (actualizaciones automáticas, por ejemplo).

Para mantenimientos en los que es necesaria la intervención del usuario, el esquema de trabajo es el siguiente:

(*
 * ----------------------------------------------------------------
 * function OcuparTodosRecursos (Cod) : boolean;
 * Cometido..: Ocupa todos los recursos que se requieren para
 *             realizar las posteriores modificaciones.
 *             Enfoque optimista.
 * ----------------------------------------------------------------
 *)
function OcuparTodosRecursos (Cod) : boolean;
begin
   TodosLibres:=true;
   strSQL:='select CD, IT_EN_USO '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   while not eof and TodosLibres do begin
      if FieldByName('IT_EN_USO').AsString = '1' then
         TodosLibres:=false
      else begin
         strSQL:='update TABLA '+
                 'set IT_EN_USO = 1 '+
                 'where CD = '+Cod+' and '+
                       'IT_EN_USO = 0';      
         NumRegsAfectados:=EjecutarSQL(strSQL);
         if NumRegsAfectados &lt; 1 then
            TodosLibres:=false;
      end;
      Next;
   end;
   OcuparTodosRecursos:=TodosLibres;
end; // OcuparTodosRecursos   (*
 * ----------------------------------------------------------------
 * procedure LiberarTodosRecursos (Cod);
 * Cometido..: Libera todos los recursos que se ocuparon para la
 *             modificación.
 * ----------------------------------------------------------------
 *)
procedure LiberarTodosRecursos (Cod);
begin
   strSQL:='select CD '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   while not eof do begin
      strSQL:='update TABLA '+
              'set IT_EN_USO = 0 '+
              'where CD = '+Cod;
      EjecutarSQL(strSQL);
      Next;
   end;
end; // LiberarTodosRecursos   (*
 * ----------------------------------------------------------------
 * procedure VolcarDatosABD (Cod, DatosModif);
 * Cometido..: Vuelca los datos modificados por el usuario a la BD.
 * ----------------------------------------------------------------
 *)
procedure VolcarDatosABD (Cod, DatosModif);
begin
   strSQL:='select CD '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   while not eof do begin
      strSQL:='update TABLA '+
              'set CAMPOS_MODIF = '+DatosModif+' '+
              'where CD = '+Cod;
      EjecutarSQL(strSQL);
      Next;
   end;
end; // VolcarDatosABD   (*
 * ----------------------------------------------------------------
 * rutina Principal;
 * Cometido..: Esqueleto de la aplicación que maneje esta forma de
 *             trabajo.
 * ----------------------------------------------------------------
 *)
rutina Principal;
begin
   StartTransaction;
   if OcuparTodosRecursos(Cod) then begin
      CommitTransaction;
      CargarDatosModificables(Cod);
      DatosModificados:=RecogerDatosAModificar(DatosModif);
      if DatosModificados = OK then begin
         StartTransaction;
         VolcarDatosABD(Cod,DatosModif);
         LiberarTodosRecursos(Cod);
         CommitTransaction;
      end;
      if DatosModificados = CANCEL then begin
         StartTransaction;
         LiberarTodosRecursos(Cod);
         CommitTransaction;
      end;
   end
   else begin
      RollBackTransaction;
      MostrarError('Los datos están siendo modificados'+
                   ' por otro usuario.'+#13+
                   'Por favor, inténtelo más tarde.');
   end;
end; // Principal

Como puede apreciarse, en el proceso existen dos transacciones. En la primera de ellas se ocupan todos los recursos que sean objeto de la modificación. Una vez ocupados todos los recursos objeto de modificación, se cierra la primera transacción. De esta forma se liberan los bloqueos establecidos por SQL Server y se potencia así la concurrencia en el acceso a los datos.

Cuando se han recogido todos los datos que el usuario quiere modificar, si éste acepta los cambios, se inicia una nueva transacción en la que, tras volcar la nueva información a la base de datos, se liberan todos los recursos que se habían ocupado. Si el usuario ha cancelado los cambios, se inicia una nueva transacción con el fin de liberar todos los recursos ocupados. Después se cierra la transacción.

Si entre la primera y la segunda transacción se produce un corte en el suministro eléctrico, el ordenador se bloquea, etc., los recursos ocupados permanecerán constantemente en este estado, ya que se cerró la transacción en la que se ocuparon. En estos casos, poco habituales por otro lado, será necesario aplicar el plan de contingencia.

El esquema del plan de contingencia sería:

(*
 * ----------------------------------------------------------------
 * procedure PlanContingencia;
 * Cometido..: Liberar todos los recursos de la BD.
 * ----------------------------------------------------------------
 *)
procedure PlanContingencia;
begin
   StartTransaction;
   for TodosLosRecursosControlados do
      LiberarTodosRecursos(Cod);
   CommitTransaction;
end; // PlanContingencia

Por otro lado, en aquellos mantenimientos en los que no se requiere interactuación con el usuario, el esquema de trabajo varía en algunos aspectos. El pseudocódigo en este caso sería:

(*
 * ----------------------------------------------------------------
 * function ModificarDatos (Cod,DatosModif) : boolean;
 * Cometido..: Modificación de datos de la BD.
 * ----------------------------------------------------------------
 *)
function ModificarDatos (Cod,DatosModif) : boolean;
begin
   StartTransaction;
   if OcuparTodosRecursos(Cod) then begin
      VolcarDatosABD(Cod,DatosModif);
      LiberarTodosRecursos(Cod);
      CommitTransaction;
      ModificarDatos:=true;
   end
   else begin
      RollBackTransaction;
      ModificarDatos:=false;
   end;
end; // ModificarDatos   (*
 * ----------------------------------------------------------------
 * rutina Principal;
 * Cometido..: Esqueleto de la aplicación que maneje esta forma de
 *             trabajo.
 * ----------------------------------------------------------------
 *)
rutina Principal;
begin
   if ModificarDatos(Cod,DatosModif) then
      Mensaje('Datos modificados')
   else
      MostrarError('Imposible modificar datos');
end; // Principal

Como puede apreciarse, en este caso, la ocupación y liberación de los recursos se produce dentro de la misma rutina y en la misma transacción, dado que ya se tienen los nuevos datos a introducir en la base de datos. Este tipo de mantenimientos no provoca problemas que hagan necesaria la ejecución del plan de contingencia, ya que todas las modificaciones sobre la base de datos se realizan dentro de la misma transacción.

Los procesos que sólo visualicen datos sin llegar a modificarlos, no tendrán en cuenta el valor de la columna IT_EN_USO, no estando para estos procesos bloqueada la fila en cuestión.

Esta forma de trabajo evita que dos procesos seleccionen a la vez los mismos datos para su posterior modificación, por lo que ningún usuario perderá tiempo modificando datos que finalmente no pueda salvar. Sin embargo, no impide que un usuario bloquee indefinidamente datos.

La pega de este esquema reside en el plan de contingencia. Para poder aplicar el proceso que liberará todas las filas bloqueadas permanentemente por un fallo en el suministro eléctrico, etc., será necesario que no haya ninguna aplicación activa excepto la que está ejecutando el administrador. Es, por tanto, aplicable en aplicaciones con pocos usuarios y en oficinas en las que se pueda controlar la actividad de todos los operarios a la vez para evitar situaciones de pérdida de datos e inconsistencias que podrían llegar a derivarse de la aplicación del plan de contingencia estando alguna aplicación activa.

Una forma de evitar el plan de contingencia manteniendo este esquema de trabajo es bajar el nivel de aislamiento de las transacciones para poder leer datos de los que todavía no se ha hecho el commit (permitiendo lecturas sucias). En este caso, no se cerraría la transacción tras ocupar los recursos, sólo se cerraría tras liberarlos, por lo que si algo falla entre el proceso de ocupar los recursos y después liberarlos, sería el propio SQL Server el encargado de deshacer los cambios, dejando de nuevo los recursos libres.

El flag “IT_EN_USO” asegura que dos usuarios no modifiquen a la vez los mismos datos. Bajar el nivel de aislamiento permite una mayor concurrencia en el acceso a los datos.

El esquema de trabajo para mantenimientos que necesiten la intervención del usuario será en este caso:

(*
 * ----------------------------------------------------------------
 * rutina Principal;
 * Cometido..: Esqueleto de la aplicación que maneje esta forma de
 *             trabajo.
 * ----------------------------------------------------------------
 *)
rutina Principal;
begin
   EjecutarSQL('set transaction isolation level readuncommitted');
   StartTransaction;
   if OcuparTodosRecursos(Cod) then begin
      CargarDatosModificables(Cod);
      DatosModificados:=RecogerDatosAModificar(DatosModif);
      if DatosModificados = OK then begin
         VolcarDatosABD(Cod,DatosModif);
         LiberarTodosRecursos(Cod);
         CommitTransaction;
      end;
      if DatosModificados = CANCEL then begin
         RollBackTransaction;
      end;
   end
   else
      RollBackTransaction;
      MostrarError('Los datos están siendo modificados'+
                   ' por otro usuario.'+#13+
                   'Por favor, inténtelo más tarde.');
   EjecutarSQL('set transaction isolation level readcommitted');
end; // Principal

El esquema de trabajo para mantenimientos que no necesiten la intervención del usuario no variará con respecto al esquema que no baja el nivel de aislamiento excepto en el hecho de que no necesita el plan de contingencia.

En las consultas que trabajen sobre las tablas manejadas con estos esquemas de bloqueo también se debe bajar el nivel de aislamiento ya que, al no cerrar la transacción hasta el final, SQL Server mantendrá bloqueos sobre dichas tablas.

Esta opción puede emplearse en contados casos ya que bajar el nivel de aislamiento de las transacciones puede ocasionar graves problemas. Un caso en el que sería medianamente fácil usar esta optimización sería en aquellos mantenimientos que sólo afectan a datos de una tabla. Si el mantenimiento afecta a tablas de tipo maestro – detalle, la programación se complica ya que las consultas que se realicen podrán leer datos sucios, en los que todavía no existan todos los detalles de un maestro, etc., pudiendo ocasionar resultados no deseados.

Supongamos ahora que mezclamos este esquema de trabajo con un enfoque pesimista. La función “OcuparTodosRecursos” podría haber sido escrita:

(*
 * ----------------------------------------------------------------
 * function OcuparTodosRecursosPesimista (Cod) : boolean;
 * Cometido..: Ocupa todos los recursos que se requieren para
 *             realizar las posteriores modificaciones.
 *             Enfoque pesimista.
 * ----------------------------------------------------------------
 *)
function OcuparTodosRecursosPesimista (Cod) : boolean;
begin
   TodosLibres:=true;
   strSQL:='select CD, IT_EN_USO '+
           'from TABLA with (repeatableread) '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   while not eof and TodosLibres do begin
      if FieldByName('IT_EN_USO').AsString = '1' then
         TodosLibres:=false
      else begin
         strSQL:='update TABLA '+
                 'set IT_EN_USO = 1 '+
                 'where CD = '+Cod;
         EjecutarSQL(strSQL);
      end;
      next;
   end;
   OcuparTodosRecursosPesimista:=TodosLibres;
end; // OcuparTodosRecursosPesimista

Podría ocurrir que dos usuarios intentaran modificar a la vez los mismos datos. En este caso, podría producirse un interbloqueo. SQL Server mataría a la transacción que ha provocado la cadena circular de bloqueo y sería necesario un tratamiento de errores especial. Con la primera de las versiones para ocupar recursos, no se producirá nunca un interbloqueo, descargando así al servidor de la tarea de tener que resolver este tipo de conflictos y evitando tener que programar un tratamiento de errores adicional en la aplicación.

Existe aún otra manera de codificar el procedimiento “OcuparTodosRecursos”.

Esta forma evita el interbloqueo, ya que lo primero que se hace es el update, con lo que se obtienen directamente los bloqueos de escritura. Además, como consecuencia de hacer un único update no se sobrecarga la red, aunque sí el servidor, por lo que es adecuada para arquitecturas en la que la red es lenta y existe un servidor potente, frente a la solución presentada en la primera solución que sería más adecuada si el servidor se encuentra ya sobrecargado.

(*
 * ----------------------------------------------------------------
 * function OcuparTodosRecursosEficiente (Cod) : boolean;
 * Cometido..: Ocupa todos los recursos que se requieren para
 *             realizar las posteriores modificaciones.
 *             Enfoque optimista.
 * ----------------------------------------------------------------
 *)
function OcuparTodosRecursosEficiente (Cod) : boolean;
begin
   strSQL:='update TABLA '+
           'set IT_EN_USO = 1 '+
           'where IT_EN_USO = 0 and '+
                 'CD = '+Cod;
   NumRegsAfectadosUpdate:=EjecutarSQL(strSQL);
   strSQL:='select count(*) as NumRegsSe '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   NumRegsAfectadosSelect:=FieldByName('NumRegsSe').AsInteger;
   if NumRegsAfectadosUpdate = NumRegsAfectadosSelect then
      OcuparTodosRecursosEficiente:=true
   else
      OcuparTodosRecursosEficiente:=false;
end; // OcuparTodosRecursosEficiente

Esta forma de ocupar los recursos, con un servidor poco cargado, es mucho más eficiente siendo este aumento de eficiencia más patente cuanto más recursos sea necesario ocupar.

Esta forma de codificación es válida también para la operación de liberar recursos.

Aunque esta codificación es más eficiente, en los ejemplos de código se seguirá manteniendo la codificación más natural del mismo para que resulte más clara su comprensión. Sin embargo, hay que tener en cuenta siempre en cuenta que esta forma de trabajo es más eficiente y ocasiona una menor carga en el servidor por resolución de contiendas de bloqueo.

El hecho de que la ocupación y liberación de recursos no sea transaccional y se necesite un plan de contingencia para los casos de bloqueo permanente hace de esta solución una primera aproximación de la solución que se expone a continuación.

Bloqueo pesimista utilizando “caducidad” de bloqueo

Partiendo del bloqueo optimista utilizando un flag para bloquear los recursos y basándose en la filosofía de trabajo de los enfoques optimistas simples, se puede plantear otra solución que tiene ciertas características que la hacen interesante.

En este caso, en aquellas tablas en las que se quiera controlar el acceso concurrente se introducirá una columna “FC_OCUPACION” de tipo datetime que, en el caso de SQL Server, ofrece una precisión de 1 milisegundo.

Se establece un tiempo de caducidad, que indicará el tiempo máximo que un proceso de modificación puede tener ocupado un recurso.

Un proceso que quiera ocupar recursos, sólo podrá hacerlo si el campo “FC_OCUPACION” del recurso a ocupar contiene una fecha caducada, es decir, si la fecha actual del servidor menos el valor de “FC_OCUPACION” supera el tiempo de caducidad. Para ocupar un recurso, el proceso introducirá la fecha actual del servidor en el campo “FC_OCUPACION” del recurso a ocupar. Esta será su marca de ocupación.

Si un mismo proceso quiere modificar varias filas, de la misma tabla o de varias, deberá obtener todos los recursos a modificar o no ocupar ninguno. En caso de no poder ocupar todos los recursos deberá liberar aquellos que ya haya ocupado y se mostrará un mensaje al usuario que le invite a intentarlo más tarde. Todos los recursos que tenga que ocupar el proceso se han de ocupar con la misma marca de ocupación.

Un proceso que haya conseguido completar con éxito todo el proceso de actualización de los datos será el encargado de liberar los mismos para que otros procesos puedan ocuparlos. Para liberar un recurso, ha de introducirse una fecha caducada en el campo “FC_OCUPACION” de cada recurso que se ha de liberar.

Llegado este punto, hay que hacer una puntualización: Una fecha no caducada indica que el recurso en cuestión está en uso. Una fecha caducada indica que el recurso no está en uso, o que el proceso que lo estaba usando ha excedido el tiempo que se le ha dado para modificar el recurso que ocupó y que, por tanto, se ha liberado el recurso (pero no lo ha liberado el proceso que lo ocupó) o, mejor dicho, que el recurso se puede ocupar.

De esta forma, un proceso que haya ocupado todos los recursos que necesite, a la hora de volcar la actualización a la base de datos, debe comprobar que la marca de ocupación sigue siendo la misma con la que ocupó los recursos. Si la marca sigue siendo la suya, podrá volcar la información, incluso aunque la marca haya caducado ya que si sigue siendo la suya significa que no hay otro proceso que haya ocupado los recursos. Por el contrario, si la marca es distinta significa que el tiempo de modificación fue excedido y que otro proceso a ocupado el recurso. En este caso, el proceso al que le ha caducado la marca no puede realizar la modificación. Este proceso debe liberar los recursos que sigan teniendo su marca de ocupación y mostrar un mensaje al usuario que le indique que no se puede realizar la actualización de los datos porque se excedió el tiempo de modificación.

Para mantenimientos en los que es necesaria la intervención del usuario, el esquema de trabajo es el siguiente:

const
   MARCA_NO_OCUPADO = '01/01/1900 00:00:00.000';
   T_CADUCIDAD = XX; // Tiempo de caducidad
(*
 * ----------------------------------------------------------------
 * function ObtenerMarcaTemp : DateTime;
 * Cometido..: Obtiene la fecha del servidor para establecer la
 *             marca de ocupación.
 * ----------------------------------------------------------------
 *)
function ObtenerMarcaTemp : DateTime;
begin
   strSQL:='select getdate() as MarcaTemp';
   EjecutarSQL(strSQL);
   ObtenerMarcaTemp:=FieldByName('MarcaTemp').AsDateTime;
end; // ObtenerMarcaTemp   (*
 * ----------------------------------------------------------------
 * function MarcaCaducada (MarcaOld, MarcaTemp): boolean;
 * Cometido..: Comprueba si la marca de modificación ha caducado
 *             o no.
 * ----------------------------------------------------------------
 *)
function MarcaCaducada : boolean;
begin
   if (MarcaTemp – MarcaOld) &gt; T_CADUCIDAD then
      MarcaCaducada:=true
   else
      MarcaCaducada:=false;
end; // MarcaCaducada   (*
 * ----------------------------------------------------------------
 * function OcuparTodosRecursos (Cod, MarcaTemp) : boolean;
 * Cometido..: Ocupa todos los recursos que se requieren para
 *             realizar las posteriores modificaciones.
 * ----------------------------------------------------------------
 *)
function OcuparTodosRecursos (Cod, MarcaTemp) : boolean;
begin
   TodosLibres:=true;
   strSQL:='select CD, FC_OCUPACION '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   while not eof and TodosLibres do begin
      MarcaOld:= FieldByName('FC_OCUPACION').AsString;
      if not MarcaCaducada(MarcaOld,MarcaTemp) then
         TodosLibres:=false
      else begin
         strSQL:='update TABLA '+
                 'set FC_OCUPACION = '+MarcaTemp+' '+
                 'where CD = '+Cod+' and '+
                       'FC_OCUPACION = 'MarcaOld+;      
         NumRegsAfectados:=EjecutarSQL(strSQL);
         if NumRegsAfectados &lt; 1 then
            TodosLibres:=false;
      end;
      Next;
   end;
   OcuparTodosRecursos:=TodosLibres;
end; // OcuparTodosRecursos   (*
 * ----------------------------------------------------------------
 * procedure LiberarTodosRecursos (Cod, MarcaTemp);
 * Cometido..: Libera todos los recursos que se ocuparon para la
 *             modificación.
 * ----------------------------------------------------------------
 *)
procedure LiberarTodosRecursos (Cod, MarcaTemp);
begin
   TodosOcupados:=true;
   strSQL:='select CD '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   while not eof and TodosOcupados do begin
      strSQL:='update TABLA '+
              'set FC_OCUPACION = '+MARCA_NO_OCUPADO+' '+
              'where CD = '+Cod+' and '
                    'FC_OCUPACION = '+MarcaTemp;
      NumRegsAfectados:=EjecutarSQL(strSQL);
      if NumRegsAfectados &lt; 1 then
         TodosOcupados:=false;
      Next;
   end;
end; // LiberarTodosRecursos   (*
 * ----------------------------------------------------------------
 * function VolcarDatosABD (Cod, MarcaTemp, DatosModif) : boolean;
 * Cometido..: Vuelca los datos modificados por el usuario a la BD.
 * ----------------------------------------------------------------
 *)
function VolcarDatosABD (Cod, MarcaTemp, DatosModif) : boolean;
begin
   TodosOcupados:=true;
   strSQL:='select CD, FC_OCUPACION '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   while not eof and TodosOcupados do begin
      if MarcaCaducada(
            FieldByName('FC_OCUPACION').AsDateTime,MarcaTemp) then
         TodosOcupados:=false;
      if TodosOcupados then begin
         strSQL:='update TABLA '+
                 'set CAMPOS_MODIF = '+DatosModif+' '+
                 'where CD = '+Cod+' and '
                       'FC_OCUPACION = 'MarcaTemp;
         NumRegsAfectados:=EjecutarSQL(strSQL);
         if NumRegsAfectados &lt; 1 then
            TodosOcupados:=false;
      end;
      Next;
   end;
   VolcarDatosABD:=TodosOcupados;
end; // VolcarDatosABD   (*
 * ----------------------------------------------------------------
 * rutina Principal;
 * Cometido..: Esqueleto de la aplicación que maneje esta forma de
 *             trabajo.
 * ----------------------------------------------------------------
 *)
rutina Principal;
begin
   StartTransaction;
   MarcaTempOc:=ObtenerMarcaTemp;
   if OcuparTodosRecursos(Cod,MarcaTempOc) then begin
      CommitTransaction;
      CargarDatosModificables(Cod);
      DatosModificados:=RecogerDatosAModificar(DatosModif);
      if DatosModificados = OK then begin
         StartTransaction;
         if VolcarDatosABD(Cod,MarcaTempOc,DatosModif) then begin
            LiberarTodosRecursos(Cod,MarcaTempOc);
            CommitTransaction;
         else begin
            RollBackTransaction;
            MostrarError('Los datos en memoria han caducado.'+
                         'Para modificar estos datos vuelva'+
                         ' a filtrar');
         end;
      end;
      if DatosModificados = CANCEL then begin
         MarcaTempLib:=ObtenerMarcaTemp;
         if not MarcaCaducada(MarcaTempOc,MarcaTempLib) then begin
            StartTransaction;
            LiberarTodosRecursos(Cod,MarcaTempOc);
            CommitTransaction;
         end;
      end;
   end
   else begin
      RollBackTransaction;
      MostrarError('Los datos están siendo modificados'+
                   ' por otro usuario.'+#13+
                   'Por favor, inténtelo más tarde.');
   end;
end; // Principal

En este caso, por poner un ejemplo, la ocupación eficiente de los recursos tendría la forma:

(*
 * ----------------------------------------------------------------
 * function OcuparTodosRecursosEficiente (Cod,MarcaTemp) : boolean;
 * Cometido..: Ocupa todos los recursos que se requieren para
 *             realizar las posteriores modificaciones con el
 *             esquema de trabajo más eficiente en servidores poco
 *             cargados.     
 * ----------------------------------------------------------------
 *)
function OcuparTodosRecursosEficiente (Cod, MarcaTemp) : boolean;
begin
   strSQL:='update TABLA '+
           'set FC_OCUPACION = '+MarcaTemp+' '+
           'where '+MarcaTemp+'-FC_OCUPACION &gt; '+T_CADUCIDAD+
                  ' and '+
                  'CD = '+Cod;
   NumRegsAfectadosUpdate:=Ejecutar(strSQL);
   strSQL:='select count(*) as NumRegsSe '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   NumRegsAfectadosSelect:=FieldByName('NumRegsSe').AsInteger;
   if NumRegsAfectadosUpdate = NumRegsAfectadosSelect then
      OcuparTodosRecursosEficiente:=true
   else
      OcuparTodosRecursosEficiente:=false;
end; // OcuparTodosRecursosEficiente

Como puede apreciarse, en el proceso existen dos transacciones. En la primera de ellas se ocupan todos los recursos que sean objeto de la modificación. Una vez ocupados todos los recursos objeto de modificación, se cierra la primera transacción. De esta forma se liberan los bloqueos establecidos por SQL Server y se potencia así la concurrencia en el acceso a los datos.

Cuando se han recogido todos los datos que el usuario quiere modificar, si éste acepta los cambios, se inicia una nueva transacción en la que, tras volcar la nueva información a la base de datos, se liberan todos los recursos que se habían ocupado. Si el usuario ha cancelado los cambios, se inicia una nueva transacción con el fin de liberar todos los recursos ocupados. Después se cierra la transacción.

Si entre la primera y la segunda transacción se produce un corte en el suministro eléctrico, el ordenador se bloquea, etc., los recursos ocupados permanecerán en este estado, hasta que su marca de ocupación caduque, por lo que en este caso no es necesario un plan de contingencia que libere los recursos, aunque es cierto que puede implementarse esta opción por si se necesitara la liberación inmediata de los recursos.

Por otro lado, en aquellos mantenimientos en los que no se requiere interactuación con el usuario, el esquema de trabajo varía en algunos aspectos. El pseudocódigo en este caso sería:

(*
 * ----------------------------------------------------------------
 * function ModificarDatos (Cod,DatosModif) : boolean;
 * Cometido..: Modificación de datos de la BD.
 * ----------------------------------------------------------------
 *)
function ModificarDatos (Cod,DatosModif) : boolean;
begin
   StartTransaction;
   MarcaTemp:=ObtenerMarcaTemp;
   if OcuparTodosRecursos(Cod,MarcaTemp) then begin
      VolcarDatosABD(Cod,MarcaTemp,DatosModif);
      LiberarTodosRecursos(Cod,MarcaTemp);
      CommitTransaction;
      ModificarDatos:=true;
   end
   else begin
      RollBackTransaction;
      ModificarDatos:=false;
   end;
end; // ModificarDatos   (*
 * ----------------------------------------------------------------
 * rutina Principal;
 * Cometido..: Esqueleto de la aplicación que maneje esta forma de
 *             trabajo.
 * ----------------------------------------------------------------
 *)
rutina Principal;
begin
   if ModificarDatos(Cod,DatosModif) then
      Mensaje('Datos modificados')
   else
      MostrarError('Imposible modificar datos');
end; // Principal

Como puede apreciarse, en este caso, la ocupación y liberación de los recursos se produce dentro de la misma transacción, dado que ya se tienen los nuevos datos a introducir en la base de datos. En este tipo de mantenimientos, el proceso que ocupa los registros siempre podrá terminar con éxito el proceso, ya que SQL Server mantiene los bloqueos que estableció sobre las filas cuando el proceso de modificación ocupó los recursos. Los procesos de este tipo deben tener la transacción abierta el menor tiempo posible para evitar reducir el nivel de concurrencia de acceso a los datos.

Los procesos que sólo visualicen datos sin llegar a modificarlos no tendrán en cuenta el valor de la columna “FC_OCUPACION”, no estando para estos procesos bloqueada la fila en cuestión.

Esta forma de trabajo impide que un usuario bloquee indefinidamente un recurso, sin embargo, puede ocasionar pérdida de tiempo de trabajo si tras introducir un gran volumen de datos no puede llevarse a cabo la modificación porque la ocupación haya caducado y otro proceso haya ocupado alguno de los recursos que necesitaba el primer proceso. Si la elección del tiempo de caducidad se ha hecho adecuadamente, esta situación no debería darse nunca, a no ser que un usuario la forzara de forma intencionada.

“Caducidad” de bloqueo sin pérdidas de actualización

En procesos muy delicados realizados por usuarios altamente cualificados que no provoquen un bloqueo innecesario de recursos y en los que exista la necesidad de interactuación con el usuario, existen dos esquemas que evitan que se pierdan datos de actualización.

El primero de estos esquemas consiste en mezclar la forma de trabajo en mantenimientos que necesitan interactuación con el usuario con la de los que no necesitan dicha interactuación, de forma que las ocupaciones no caduquen. En este caso el pseudocódigo sería:

const
   DATOS_MODIF    = 1;
   DATOS_CANCEL   = 2;
   DATOS_OCUPADOS = 3;   (*
 * ----------------------------------------------------------------
 * function ModificarDatos (Cod) : integer;
 * Cometido..: Modificación de datos de la BD.
 * ----------------------------------------------------------------
 *)
function ModificarDatos (Cod) : integer;
begin
   StartTransaction;
   MarcaTempOc:=ObtenerMarcaTemp;
   if OcuparTodosRecursos(Cod,MarcaTempOc) then begin
      CargarDatosModificables(Cod);
      DatosModificados:=RecogerDatosAModificar(DatosModif);
      if DatosModificados = OK then begin
         VolcarDatosABD(Cod,MarcaTempOc,DatosModif);
         LiberarTodosRecursos(Cod,MarcaTempOc);
         CommitTransaction;
         ModificarDatos:=DATOS_MODIF;
      end;
      if DatosModificados = CANCEL then begin
         RollBackTransaction;
         ModificarDatos:=DATOS_CANCEL;
      end;
   end
   else begin
      RollBackTransaction;
      ModificarDatos:=DATOS_OCUPADOS;
   end;
end; // ModificarDatos   (*
 * ----------------------------------------------------------------
 * rutina Principal;
 * Cometido..: Esqueleto de la aplicación que maneje esta forma de
 *             trabajo.
 * ----------------------------------------------------------------
 *)
rutina Principal;
begin
   Resultado:=ModificarDatos(Cod);
   if Resultado = DATOS_MODIF then
      Mensaje('Datos modificados');
   if Resultado = DATOS_CANCEL then
      Mensaje('Modificación cancelada');
   if Resultado = DATOS_OCUPADOS then
      MostrarError('Los datos están siendo modificados'+
                   ' por otro usuario.'+#13+
                   'Por favor, inténtelo más tarde.');
end; // Principal

Como puede apreciarse, todo el proceso se realiza dentro de la misma transacción por lo que SQL Server mantiene los bloqueos establecidos sobre los recursos que bloqueó el proceso hasta el final de la misma. De esta forma, ningún otro proceso podrá bloquear los recursos, aunque la marca haya caducado. De hecho, otro proceso que intentara bloquear los recursos mediante el enfoque optimista se quedaría bloqueado hasta que se terminara la transacción.

Este esquema de trabajo sólo debe implementarse en aquellos procesos extremadamente delicados y cortos en duración, realizados por personal altamente cualificado que no provoque bloqueos innecesarios de los recursos dado que, al mantener los bloqueos establecidos por SQL Server hasta el final de la transacción, ningún otro proceso podrá bloquear dichos recursos hasta la finalización de la misma, llegando incluso a quedarse bloqueados hasta ese momento, reduciéndose de esta forma el nivel de concurrencia de acceso a los datos.

El segundo de estos esquemas presenta la ventaja de que no provocará que ningún otro proceso quede bloqueado por SQL Server, aunque representa una mayor carga para el gestor de bases de datos.

Consiste en asociar un temporizador al formulario en el que se están recogiendo los datos a actualizar de forma que, cuando se dispare éste, se actualice el campo en el que se almacena la caducidad del bloqueo, antes de que se produzca dicha caducidad. De esta forma, otros procesos podrán visualizar datos sin llegar a bloquearse y se asegura que el proceso que ocupó los recursos finalice exitosamente la actualización de los datos.

Del Sr. Alcalde

Posted in .NET, DataAccess | Etiquetado: | 1 Comment »

Transacciones: Gestión de bloqueos en SQL Server

Posted by kiquenet en 18 septiembre 2010

Se ha comentado el funcionamiento de las transacciones en este gestor de base de datos (SGBD). Sin embargo, para acabar de comprender su funcionamiento debemos conocer cómo se manejan los bloqueos en SQL Server. El correcto funcionamiento y la eficiencia de nuestra aplicación que maneja usuarios concurrentes accediendo a nuestra base de datos dependen de la configuración de SQL Server y de la programación de la aplicación, así que, tanto para una cosa como para la otra, debemos comprender y manejar estos conceptos.

Bloqueos y rendimiento

Los bloqueos son los encargados de hacer que las transacciones tengan la propiedad de aislamiento. La realización de bloqueos en un entorno concurrente proporciona aislamiento en los siguientes casos:

  • Múltiples transacciones intentan modificar la misma fila.
  • Datos no confirmados por una transacción son leídos por otras transacciones. Esto se conoce como lecturas sucias.
  • Los datos leídos con la misma cláusula “select” durante varias etapas de la transacción dan lugar a valores diferentes con el mismo conjunto de datos. Conocido como lecturas no repetibles.
  • Los datos leídos con la misma cláusula “select” durante varias etapas de la transacción dan lugar a diferentes conjuntos de datos. Es lo que se conoce como “lecturas fantasma”.

El establecimiento de los bloqueos es automático en SQL Server, no teniéndose que preocupar, por tanto, el programador directamente por ellos, aunque debe tener en cuenta qué tipo de bloqueos establece cada operación atendiendo a las cláusulas incluidas, etc., para minimizar los interbloqueos.

Recursos de los bloqueos. Granularidad del bloqueo

El establecimiento de bloqueos es automático en SQL Server. El gestor de bloqueos es el encargado de decidir el tipo y la granularidad del bloqueo.

Los bloqueos se pueden producir tanto a nivel de página de datos como a nivel de página de índice. El gestor de bloqueo puede decidir bloquear los recursos: tabla, extensión, página, fila y rango de clave.

La granularidad del bloqueo incide de forma directa sobre la concurrencia y el rendimiento del sistema. El optimizador de consultas decide la granularidad del bloqueo automáticamente. No obstante, SQL Server proporciona extensiones de T-SQL que proporcionan un mecanismo para controlar la granuralidad de los bloqueos.

Tipos de bloqueo en SQL Server

El procedimiento almacenado “sp_lock” proporciona los bloqueos activos en el sistema gestor de bases de datos. También ofrecen información sobre bloqueos el administrador corporativo, el monitor de rendimiento, el administrador de memoria y el objeto de SQL Server bloqueos.

Dependiendo del tipo de transacción, el gestor de bloqueos establece distintos tipos de bloqueos.

Bloqueos compartidos

Se establecen para operaciones de sólo lectura, por ejemplo “select”.

Varias transacciones pueden mantener varios bloqueos compartidos simultáneamente sobre el mismo recurso ya que dichas transacciones no interfieren entre sí.

Si una transacción intenta modificar un dato de un recurso sobre el que se ha establecido un bloqueo compartido, se bloqueará hasta que todos los bloqueos compartidos se liberen.

Un bloqueo compartido se libera en cuanto se finaliza la operación de lectura de datos, es decir, no permanece a lo largo de la transacción. Existen cláusulas de las que se hablará más adelante para modificar este comportamiento.

Bloqueos de actualización

Los bloqueos de actualización se usan para instrucciones que modifican datos.

Cuando una transacción intenta actualizar una fila primero ha de leerla para asegurarse de que la fila en cuestión es realmente la que se quiere modificar. En este momento establece un bloqueo compartido. Una vez se ha asegurado que es la fila correcta procederá a modificarla, para lo que necesita establecer un bloqueo exclusivo que será liberado al terminar la transacción. Los bloqueos de actualización se emplean como bloqueo intermedio entre los dos anteriores para evitar interbloqueos.

Bloqueos exclusivos

Se concede un bloqueo de este tipo a una transacción cuando ésta está lista para modificar los datos. Un bloqueo exclusivo sobre un recurso asegura que ninguna otra transacción pueda interferir las acciones llevadas a cabo por la primera transacción sobre los recursos bloqueados.

SQL Server libera el bloqueo al finalizar la transacción.

Bloqueos de intención

El bloqueo de intención es un mecanismo que utilizan las transacciones para declarar su intención de obtener un bloqueo compartido, de actualización o exclusivo sobre un recurso.

No establece ningún bloqueo en si, excepto por el hecho de que otra transacción no podrá adquirir un bloqueo de actualización sobre un recurso sobre el que se haya establecido un bloqueo de intención.

Bloqueos de esquema

Se utilizan para mantener la integridad estructural de las tablas SQL Server. A diferencia de otros bloqueos, que proporcionan aislamiento para los datos, los bloqueos de esquema proporcionan aislamiento para el esquema de objetos de la base de datos.

Compatibilidad de bloqueos

En la siguiente lista se muestra la compatibilidad entre los distintos tipos de bloqueo. Para cada bloqueo que puede llegar a estar activo sobre una conexión al sistema gestor de bases de datos SQL Server se lista qué tipos de bloqueo pueden solicitarse concurrentemente (por ejemplo, si existe un bloqueo de modificación de esquema no puede solicitarse ningún otro tipo de bloqueo, tal y como muestra el último elemento de esta lista).

  • De intención compartido: de intención compartido, compartido, de actualización, de intención exclusivo, compartido de intención exclusivo y de estabilidad de esquema.
  • Compartido: de intención compartido, compartido, de actualización y de estabilidad de esquema.
  • De actualización: de intención compartido, compartido y de estabilidad de esquema.
  • De intención exclusivo: de intención compartido, de intención exclusivo y de estabilidad de esquema.
  • Compartido de actualización exclusivo: de intención compartido y de estabilidad de esquema.
  • Exclusivo: estabilidad de esquema.
  • Estabilidad de esquema: de intención compartido, compartido, de actualización, de intención exclusivo, compartido de intención exclusivo, exclusivo y de estabilidad de esquema.
  • Modificación de esquema: ningún bloqueo es compatible con el bloque de modificación de esquema.
Bloqueos de índices

De forma similar a los bloqueos de páginas de datos, SQL Server gestiona los bloqueos de las páginas de índices internamente.

Con el comando “sp_indexoption” se puede modificar el gestor permitiendo o no bloqueos a nivel de filas.

Se recomienda no realizar modificaciones sobre el comportamiento por defecto del gestor de bloqueos en lo referente a las páginas de índices ya que suele hacer buenas elecciones.

Notas:

Todas las técnicas comentadas tratan en todo momento de mantener la integridad de la base de datos si no, solucionar el problema de mejorar el acceso concurrente nos crearía un problema más grave aún.

A esto que acabo de decir, tengo que añadir dos matices:
El primero de ellos es que siempre hay que tener presente que las cosas que hagas se pueden hacer bien o mal. Por ejemplo, cambiar los niveles de aislamiento de las transacciones puede provocar el colapso de la base de datos, que tus aplicaciones empiecen a mostrar datos sobre los que no se ha hecho commit (que puede ser un problema o no)… En fin, que hay que saber lo que se está haciendo.
El segundo es, en parte, continuación de lo anterior, y es que cada solución, como todo en este mundo tiene sus cosas buenas y sus cosas malas por lo que se adaptan mejor o peor a un tipo de problema determinado, dependiendo del coste de implementación, de la forma en que resuelven el problema de bloqueo, etc. Por poner un ejemplo, la solución más “sencillita” que sería utilizar un flag con un plan de contingencia, puede dejar registros bloqueados en el caso que comentas, por eso es necesario un plan de contingencia que consiste en una aplicación auxiliar que libere esos registros en caso de que se vaya la luz, etc… Sin embargo, implementarla es muy sencillo y no hay que controlar ningún otro efecto colateral… El bloqueo pesimista utilizando caducidad del bloqueo es, en general, el que mejor se ajusta a todo tipo de problemas pero requiere más implementación…

Como comentaba, todo depende del problema en cuestión.

Del Mister Alcalde

Posted in .NET, DataAccess | Etiquetado: , , | Leave a Comment »

Transacciones: Gestión de transacciones en SQL Server

Posted by kiquenet en 18 septiembre 2010

Conocer la gestión de transacciones que realiza SQL Server, qué son y cómo funcionan, es la clave para desarrollar aplicaciones eficaces y eficientes que se conectan a un gestor de bases de datos (SGBD) para consultar y modificar datos, con usuarios accediendo concurrentemente para realizar acciones de distinta naturaleza sobre los mismos (no es lo mismo consultar información que actualizarla) y teniendo que gestionar acciones de actualización que afectan a varios datos a la vez.

Procesamiento de transacciones

Una transacción es una unidad lógica de trabajo que tiene ciertas propiedades conocidas como propiedades ACID: atomicidad, coherencia, aislamiento y permanencia.

Es responsabilidad del sistema gestor de bases de datos el asegurar que se cumplan estas propiedades. Sin embargo, el programador dispone de un cierto grado de control sobre la forma en que SQL Server gestiona algunas de estas propiedades.

Existen tres formas de llevar a cabo este procesamiento: autoconfirmación, explícita e implícita (que no voy a abordar en este post pero que os animo a comentar si lo creéis interesante).

Transacciones y bloqueos

SQL Server realiza y mantiene una serie de bloqueos durante la transacción. Existen dos formas de modificar el tratamiento de los bloqueos.

La primera de ellas consiste en introducir cláusulas en las sentencias “select”. Estas cláusulas modificarán el tratamiento de los bloqueos de las unidades afectadas (filas, tablas, páginas; la granularidad también es seleccionable y se tratará más adelante) por la sentencia y se mantendrán hasta la finalización de la transacción.

La segunda, se mantiene durante toda la sesión. Para modificar la forma en que una sentencia “select” gestiona una transacción y sus bloqueos se utiliza la sentencia “set transaction isolation level” que tiene las siguientes opciones o niveles de aislamiento, mutuamente excluyentes:

  • “read committed”: Es el comportamiento predeterminado. La transacción mantiene bloqueos compartidos durante el tiempo que dure la lectura. La transacción puede encontrarse con lecturas no repetibles o datos fantasma dado que el bloqueo no impide a otras transacciones modificar los datos que se han leído.
  • “read uncommitted”: Con este nivel de aislamiento una transacción puede leer datos sucios de otras transacciones. Es el nivel de aislamiento menos restrictivo. Esta opción tiene el mismo efecto que usar la opción “nolock” en una sentencia “select”.
  • “repeatable read”: A medida que se leen datos se establecen bloqueos de forma que otra transacción no pueda modificar datos leídos. Este nivel de aislamiento es más restrictivo y puede hacer que otras transacciones se bloqueen. No evita que se puedan añadir nuevas filas.
  • “snapshot”: Con este nivel de aislamiento las consultas de la transacción en curso sólo leerán los datos confirmados antes del comienzo de la misma. Dado que la transacción en curso trabaja con una instantánea de los datos, capturada en el momento de inicio de la transacción, las lecturas de datos de esta transacción no solicitan bloqueos y otras transacciones que realicen operaciones de escritura no bloquearán a ésta.
  • “serializable”: Nivel de aislamiento más restrictivo ya que aplica un bloqueo al rango de datos leídos. Proporciona, por tanto, protección ante lectura de filas fantasma.
Supervisión de transacciones

La supervisión de transacciones se apoya en el registro de transacciones. Para llevar la cuenta del número de transacciones abiertas, SQL Server utiliza una variable global de sesión denominada “@@trancount”.

Codificación de transacciones eficientes

Las transacciones tienen un efecto negativo en la concurrencia de acceso a datos. Para reducir la contienda de bloqueo sobre los recursos, las transacciones deben ser tan cortas y eficientes como sea posible. A continuación se enumeran algunas reglas que se deben observar al programar transacciones:

  • No devolver conjuntos de resultados dentro de una transacción. Dicha acción prolonga la transacción innecesariamente. Se debe realizar la recuperación y análisis de los datos fuera de la transacción.
  • Se debe evitar, en la medida de lo posible, pedir al usuario una respuesta cuando se haya iniciado una transacción. En caso de error se debe cerrar primero la transacción y después mostrar el mensaje de error.
  • La transacción debe ser lo más corta posible. Se debe abrir justo en el momento en el que se deseen realizar las modificaciones y cerrarla justo después de haberlas realizado.
  • Iniciar y terminar transacciones es un proceso que necesita una gran cantidad de trabajo por parte del servidor, por ello se deben abrir transacciones únicamente cuando sea necesario.

Conexiones acopladas

Como se ha comentado anteriormente, las transacciones tienen un efecto negativo en la concurrencia al acceso de datos.

Si es necesario implementar una transacción de larga duración manteniendo, por tanto, bloqueos exclusivos, y que continúe siendo posible algún tipo de procesamiento operativo, las conexiones acopladas o enlazadas pueden ser una solución. Existen dos tipos de conexiones acopladas: locales y distribuidas:

Las conexiones acopladas locales son conexiones dentro de un único servidor que comparten un espacio de transacciones.

Las conexiones acopladas distribuidas hacen uso del coordinador de transacciones distribuidas para que se comparta el mismo espacio de transacciones entre conexiones procedentes de más de un servidor.

Las conexiones enlazadas son útiles al desarrollar procedimientos almacenados extendidos que deben ejecutar instrucciones de Transact-SQL en nombre del proceso que las llama. Pasar el proceso que realiza la llamada en un testigo enlazado como un parámetro del procedimiento almacenado extendido permite al procedimiento combinar el espacio de transacciones del proceso que realiza la llamada y, por ello, integrar el procedimiento almacenado extendido con el proceso que realiza la llamada.

Se puede utilizar conexiones enlazadas para desarrollar aplicaciones en tres estratos en las que la lógica comercial se representa en programas independientes que funcionan en colaboración en una sola transacción comercial.

El ejemplo siguiente de conexiones enlazadas ilustra cómo pueden tener acceso dos conexiones a la misma transacción: Un cliente decide adquirir un producto a unos grandes almacenes locales. El vendedor obtiene acceso al sistema de transacciones de ventas que inserta una fila en la tabla de transacciones de ventas e incluye un número de autorización de tarjeta de crédito. Se realizan dos conexiones con el mismo servidor, la conexión C1 y la conexión C2. C1 comienza una transacción que agrega una fila de venta de productos a la tabla de ventas. Se debe agregar un número de autorización de tarjeta de crédito a la nueva fila de transacciones de ventas. Durante el proceso de autorización de la tarjeta de crédito, el procedimiento almacenado extendido crea la conexión C2 para conectar mediante la línea telefónica con la compañía de tarjetas de crédito y modifica la fila de transacciones de ventas con el número de autorización de la tarjeta de crédito. Solamente mediante la utilización de conexiones enlazadas, ambas conexiones pueden tener acceso a la misma fila sin que se produzcan conflictos de bloqueo.

Existe una importante desventaja de las transacciones acopladas: el procesamiento secuenciado. Sólo una de entre todas las conexiones acopladas puede estar realizando una operación. Cualquier intento de efectuar una tarea mientras que la anterior operación está teniendo lugar dará como resultado un error, que debe detectarse para su posterior tratamiento.

Transacciones distribuidas

Dada la creciente utilización de los sistemas distribuidos, existe la necesidad de acceder y modificar datos distribuidos.

Es necesaria una forma de asegurar que la transacción distribuida opere de la misma forma que lo haría una transacción local y que respete las propiedades ACID de cualquier transacción.

SQL Server proporciona las sentencias “begin distributed transaction” para comenzar una transacción distribuida.

El manejo de estas transacciones se realiza mediante el protocolo de confirmación en dos fases.

Del Profesor Señor Alcalde

Posted in .NET, DataAccess | Etiquetado: | 1 Comment »

El maravilloso mundo de las Transacciones II

Posted by kiquenet en 18 septiembre 2010

En el analizador de consultas de SQL SERVER:

begin tran

select * from CORC_TEMP_COMPROMISOS where it_accion = ‘M’
select @@trancount

select * from CORC_TEMP_COMPROMISOS where it_accion = ‘A’
select @@trancount

Insert into CORC_COMPROMISOS (cd_linea_financiera, cd_asociacion,
cd_accion, nm_grupos, nm_participantes)
values ( ‘5’,’0001′,33,33,33)
— falla por triggers , es indiferente para el commit/rollbak de la transacción

select @@trancount — aqui vale 0 !!!!!!!!!! DEBERIA VALER 1 !!!!!
commit tran

Es responsabilidad del programador de Transact-SQL utilizar COMMIT
TRANSACTION sólo en el punto donde todos los datos a los que hace referencia la transacción sean lógicamente correctos.

Si la transacción que se ha confirmado era una transacción Transact-SQL
distribuida, COMMIT TRANSACTION hace que MS DTC utilice el protocolo de confirmación en dos fases para enviar confirmaciones a los servidores
involucrados en la transacción. Si una transacción local afecta a dos o más
bases de datos del mismo servidor, SQL Server utiliza una confirmación
interna en dos fases para confirmar todas las bases de datos involucradas en la transacción.

Cuando se utiliza en transacciones anidadas, las confirmaciones de las transacciones anidadas no liberan recursos ni hacen permanentes sus modificaciones.

Las modificaciones sobre los datos sólo quedan permanentes y se liberan los recursos cuando se confirma la transacción más externa.

Cada COMMIT TRANSACTION que se ejecute cuando @@TRANCOUNT sea mayor que 1 sólo reduce @@TRANCOUNT en 1.

Cuando @@TRANCOUNT llega a 0, se confirma la transacción externa entera. Como SQL Server omite transaction_name, la ejecución de una instrucción COMMIT TRANSACTION que haga referencia al nombre de una transacción externa cuando haya transacciones anidadas pendientes sólo reduce @@TRANCOUNT en 1.

La ejecución de COMMIT TRANSACTION cuando @@TRANCOUNT es 0 produce un error que indica que no hay ninguna instrucción BEGIN TRANSACTION asociada.

No se puede cancelar una transacción después de ejecutar una instrucción
COMMIT TRANSACTION, porque las modificaciones sobre los datos ya son parte permanente de la base de datos.

Este ejemplo utiliza @@TRANCOUNT para comprobar si hay transacciones
abiertas que haya que confirmar.

BEGIN TRANSACTION
UPDATE authors SET au_lname = upper(au_lname)
WHERE au_lname = ‘White’

IF @@ROWCOUNT = 2
COMMIT TRAN

IF @@TRANCOUNT > 0
BEGIN
PRINT ‘A transaction needs to be rolled back’
ROLLBACK TRAN
END

Fuentes: V.Popov (Foros Microsoft)

Posted in .NET, DataAccess | Etiquetado: | Leave a Comment »

El maravilloso mundo de las Transacciones

Posted by kiquenet en 21 julio 2010

Nested Transaction Scopes

three TransactionScopeOption settings:

  • Required: the contained code depends on a transaction for atomicity, isolation, and compensation, and could be part of a broader transaction.
  • RequiresNew: the contained code depends on a transaction, but must be independently committed or rolled back.
  • Suppress: the contained code performs its own compensation, so must not be part of a transaction.

The first setting is the default, under the assumption that if you are creating a TransactionScope, you probably want a transaction. In that case, the normal situation is one where the operation you’re about to do can be reasonably integrated into a transaction that already active.

The second setting is for cases where the contained code block does require a transaction for its consistency, and provides a feature that demands that it be separate from any transaction that might already be active. One typical example would be a function that provides activity logging to, say, a database. It may be implemented such that it required a transaction to provide consistency, but it couldn’t accept an outer rollback to undo the record of the attempted activity.

The final setting, Suppress, handles case where the contained code needs to be sure that it is not executing as part of any transaction. This is fairly uncommon for a local operations — the only real case for this would be if the contained code was designed to handle its own compensation, yet used recoverable resources, such as SQL, to do its actions.

However, the reality is slightly more subtle. Complete means just what the name suggests — that the code within the TransactionScope has completed (successfully) all the operations it intended to do as part of the transaction. Note that this is also why you can’t call Complete twice. To do so would mean that at least one of the two calls wasn’t the last intended operation.

When a TransactionScope is disposed it first looks to see if it was completed successfully. If it was not, the transaction is immediately rolled back. If it was, and this was the TransactionScope that created the transaction in the first place, the transaction is committed. Finally, in both cases, the current transaction is replaced with the value that was current when the TransactionScope was created.

Thus we have, for success:

// no transaction is active here
using (TransactionScope s = new TransactionScope ())
{
    // transaction ‘a’ is active here
    using (TransactionScope t = new TransactionScope
                (TransactionScopeOption.RequiresNew))
    {
        // transaction ‘b’ is active here
        t.Complete ();
    } // the transaction from ‘s’ is put into Current.

    // transaction ‘a’ is active here
    using (TransactionScope u = new TransactionScope ())
    {
        // transaction ‘a’ is active here
        u.Complete ();
    } // the transaction from ‘s’ is put into Current.

    // transaction ‘a’ is active here
    s.Complete ();
}

// ‘a’ commits at this point
// no transaction is active here

For a failure case we have:

// no transaction is active here
using (TransactionScope s = new TransactionScope ())
{
    // transaction ‘a’ is active here
    using (TransactionScope t = new TransactionScope(
                  TransactionScopeOption.RequiresNew))
    {
        // transaction ‘b’ is active here
        t.Complete ();
    }

    // transaction ‘a’ is active here
    using (TransactionScope u = new TransactionScope ())
    {
        // transaction ‘a’ is active here
        //u.Complete ();
    } // ‘a’ rolls back here,
      //the transaction from ‘s’ is put into Current.

    // transaction ‘a’ is current, but aborted here
    s.Complete ();

}  // ‘a’ is already aborted, so is just removed at this point
// no transaction is active here

There was a massive upgrade to the error handling within SQL Server 2005. These articles are fairly extensive:

Error Handling in SQL 2005 and Later by Erland Sommarskog
http://www.sommarskog.se/error_handling_2005.html

Error Handling in SQL 2000 – a Background by Erland Sommarskog
http://www.sommarskog.se/error-handling-I.html

http://stackoverflow.com/questions/2701934/transactionscope-and-transactions

MSDN:

http://msdn.microsoft.com/en-us/library/ms973865.aspx

Posted in .NET, DataAccess | Etiquetado: , | Leave a Comment »