Comparison of concurrency control (LOCK) between DB2 and Oracle

Comparison of concurrency control (LOCK) between DB2 and Oracle

Comparison of concurrency control (LOCK) between DB2 and Oracle

1 Introduction
The minimal recovery and transaction units in relational databases (DB2, Oracle, Sybase, Informix, and SQL Server) are a transaction (Transactions). Transactions are ACID (atomicity, consistency, isolation, and durability) feature. In order to ensure the correctness of concurrent users when accessing the same database object (that is, no loss updates, repeatable reads, no dirty data reads, no Phantom reads ), the database introduces the concurrency (LOCK) mechanism. There are two basic lock types: Exclusive locks (marked as X locks) and Share locks (marked as S locks ).
Exclusive lock: If transaction T applies the X lock to data D, no other transaction can apply any type lock to D Until T releases the X lock on D; generally, an exclusive lock must be applied to the data before the data is modified. Therefore, an exclusive lock is also called a write lock.
Share lock: If transaction T adds S lock to data D, other transactions can only apply S lock to D, but cannot apply X lock until T releases S lock on D; generally, you need to apply a shared lock to the data before reading the data. Therefore, a shared lock is also called a read lock.
2 Introduction to the multi-granularity blocking mechanism of DB2
2.1 Lock Object
DB2 supports locking table space, tables, rows, and indexes (databases on the mainframe can also support locking data pages) to ensure the concurrent integrity of the database. However, when considering the concurrency of your applications, we usually do not check the locks used for tablespaces and indexes. This type of problem analysis focuses on table locks and row locks.
2.2 lock Policy
DB2 can lock only the table or the rows in the table. If only the table is locked, all rows in the table are affected to the same extent. If the lock range is for tables and subordinate rows, the corresponding data rows will be locked after the table is locked. Whether an application adds a row lock or a table lock or a row lock is determined by the Command executed by the application and the isolation level of the system.
2.2.1 DB2 table lock mode
For Table-level locking, DB2 can use the following locking methods:

Table 1: DB2 database table lock mode

The following describes several table lock modes:
The IS, IX, and SIX methods are used at the table level and require row lock cooperation. They can prevent other applications from adding an exclusive lock to the table.
• If an application obtains the IS lock of a table, the application can obtain the S lock on a row for read-only operations, and other applications can also read this row, or modify other rows in the table.
• If an application acquires the IX lock of a table, the application can obtain the X lock on a row for change operations, at the same time, other applications can read or modify other rows in the table.
• If an application acquires the SIX lock of a table, the application can obtain the X lock on a row for change operations, at the same time, other applications can only perform read-only operations on other rows in the table.
The S, U, X, and Z modes are used at the table level, but do not require row lock cooperation. They are relatively strict table lock policies.
• If an application gets the S lock of a table. This application can read any data in the table. It also allows other applications to obtain the read-only request lock for the table. If an application needs to change the data read from the table, it must wait until the S lock is released.
• If an application obtains the U Lock of a table, the application can read any data in the table and obtain the X lock of the table to modify any data in the table. Other applications can only read data from this table. The difference between the U Lock and the S lock lies in the intention of modification. The U Lock is designed to prevent two applications from simultaneously applying for the X lock when they own the S lock.
• If an application gets the X lock on a table, the application can read or modify any data in the table. Other applications cannot read or modify the table.
• If an application gets the Z lock on a table, the application can read or modify any data in the table. Other applications, including uncommitted read programs, cannot read or modify the table.
The IN lock is used for the table to allow uncommitted read.
2.2.2 DB2 row lock mode
In addition to table locks, DB2 also supports row locks in the following ways.

Table 2: DB2 database row lock mode

2.2.3 compatibility of DB2 locks

Table 3: Compatibility Matrix of DB2 database table locks


Table 4: Compatibility Matrix of DB2 database row locks

The following table lists the table locks generated by SQL statements in DB2 (assuming the default isolation level is CS ):


2.3 DB2 lock upgrade
Each lock requires a certain amount of memory space in the memory. To reduce the memory overhead required by the lock, DB2 provides the lock upgrade function. A lock upgrade adds a non-intention table lock to the table and releases the row lock to reduce the number of locks, thereby reducing the memory overhead required by the lock. The lock upgrade is automatically completed by the Database Manager. The configuration parameters of two databases directly affect the lock upgrade process:
Locklist -- the memory used to lock storage in the global memory of a database. The Unit is page (4 K ).
Maxlocks-the percentage of memory occupied by locklist allowed by an application.
The lock upgrade is triggered in either of the following situations:
• The memory space occupied by the locks requested by an application exceeds the product size of maxlocks and locklist. In this case, the database manager tries to save space by applying for a lock request for the application and releasing the row lock.
• The memory space occupied by all locks added to a database exceeds the size defined by locklist. In this case, the database manager will try to save space by applying for a lock request for the application and releasing the row lock.
• Although the lock upgrade will reduce the concurrent performance of the OLTP application, the lock upgrade will release the lock to occupy the memory and increase the memory space of the available lock.
Lock upgrades may fail. For example, an application now has an IX lock on a table, and some rows in the table have an X lock, another application requests the IS lock on the table and the S lock on many rows, which causes the lock upgrade due to the number of applied locks. The database manager tries to reduce the number of required locks for the S lock on the application form. However, the S lock conflicts with the original IX lock on the table, and the lock upgrade fails.
If the lock upgrade fails, the application that causes the lock upgrade will receive a-912 SQLCODE. After the lock upgrade fails, DBA should consider increasing the locklist size or increasing the maxlocks percentage. At the same time, programmers can roll back the program after the lock upgrade and resubmit the transaction (for example, if sqlca. sqlcode =-912 then rollback and retry) in the program ).
3 Introduction to Oracle multi-granularity lock mechanism
Oracle Database locks can be divided into the following categories based on different protected objects:
(1) DML lock (data locks): used to protect data integrity;
(2) DDL lock (dictionary locks): used to protect the structure of database objects (such as the schema definitions of tables, views, and indexes );
(3) Internal locks and latches (Internal locks and latches): protects the Internal database structure;
(4) Distributed locks: Used in OPS (Parallel Server;
(5) PCM locks: Used in OPS (Parallel Server.
The most important lock in Oracle is the DML (also known as data locks) Lock. From the perspective of the lock granularity (the size of the Lock Object), Oracle DML locks have two levels: Row-level locks and table-level locks.
3.1 Oracle TX locks (Row-level locks and transaction locks)
Many technical staff who are not familiar with Oracle may think that each TX lock represents a blocked data row. The original meaning of TX is Transaction. When a Transaction executes data changes (Insert, Update, Delete) for the first time or uses SELECT... When a for update statement is queried, it acquires a TX (transaction) lock until the transaction ends (executing the COMMIT or ROLLBACK operation. Therefore, a TX lock can correspond to multiple data rows locked by the transaction (when we use it, we usually start a transaction, and then SELECT... For update nowait ).
Each row of Oracle data has a flag to indicate whether the row data is locked. Unlike DB2, Oracle creates a linked list to maintain the locked data of each row, which greatly reduces the row-Level Lock maintenance overhead, to a large extent, It also avoids the situation where the number of locks that often occur when DB2 uses row-level locks is insufficient. Once the lock mark on the Data row is set to a bit, it indicates that the row data is locked by X, and Oracle does not have the S lock on the Data row.
3.2 tmlock (Table-Level Lock)
3.2.1 introduction of intention lock
A table is composed of rows. When locking a table, we need to check whether the lock application is compatible with the original table-Level Lock, check whether the lock is compatible with the lock on each row in the table. For example, a transaction needs to apply the S lock to a table. If an row in the table has been locked by another transaction, the application for the lock should also be blocked. If there is a lot of data in the table, the overhead of checking the lock mark row by row will be very large, and the system performance will be affected. To solve this problem, a new lock type can be introduced at the table level to indicate the locking of the row to which the lock belongs. This introduces the concept of "intention lock.
The intention lock means that if an intention lock is applied to a node, the lower node of the node is being locked, the intention lock must be applied to its upper-Layer Nodes first. For example, when locking any row in the table, you must first apply an intention lock to the table where the row is located, and then apply a lock to the row. In this way, when a transaction locks a table, the lock flag of each record in the table is no longer required, and the system efficiency is greatly improved.
3.2.2 type of intention lock
Two basic lock types (S lock and X lock) can naturally generate two intention locks:
Intent Share Lock: If you want to apply the S Lock to a database object, you must first apply the IS Lock to its parent node, it indicates that S locks are applied to the descendant node of it;
Intent Exclusive Lock (IX Lock): To apply an X Lock to a database object, you must first apply an IX Lock to its parent node, indicates that X lock is applied to the child node (intention.
In addition, a new lock type can be combined between the basic lock types (S, X) and the intention lock types (IS, IX). In theory, four types can be combined: S + IS, S + IX, X + IS, X + IX. However, it IS not difficult to find that only S + IX has a new meaning, none of the other three combinations increase the lock strength (I .e., S + IS = S, X + IS = X, X + IX = X, "=" indicates that the lock strength is the same ). The so-called lock strength refers to the degree of rejection of other locks.
In this way, we can introduce a new lock type:
Shared Intent Exclusive Lock (SIX Lock): If the SIX Lock is applied to a database object, the S Lock and the IX Lock are applied to it, that is, SIX = S + IX. For example, if a transaction adds a SIX lock to a table, it means that the transaction needs to read the entire table (so it needs to apply the S lock to the table ), at the same time, individual rows will be updated (so the IX lock should be applied to the table ).
In this way, there may be five lock types applied to database objects: S, X, IS, IX, and SIX.
Any transaction T in the Multi-granularity blocking method with intention lock needs to lock a database object, and the intention lock must be applied to its upper-Layer Nodes first. The application should be blocked in the top-down order; the release should be in the bottom-up order; the multi-granularity blocking method with intention locks improves the concurrency of the system, reduces the overhead of locking and unlocking.
3.3 Oracle tmlock (Table-Level Lock)
Oracle's DML lock (Data lock) adopts the multi-granularity lock method mentioned above. Although there is only one row-Level Lock (X lock), its tmlock (Table-Level Lock) there are five types, namely, shared lock (S lock), exclusive lock (X lock), row-level shared lock (RS lock), and row-level exclusive lock (RX lock) the shared row-level exclusive lock (SRX lock) corresponds to the S, X, IS, IX, and SIX mentioned above. Note that since Oracle only provides the X lock at the row level... The row-Level Lock corresponding to the for update statement IS also the X lock (but the row data IS not modified yet), which IS different from the theoretical IS lock. When an application adds a lock to a table (ROW), can other applications add a lock to the table (ROW, the two locks are compatible. Otherwise, the two locks are incompatible and cannot be concurrently accessed to the same data object.
The following table shows the compatibility matrix of Oracle Database tmlock (Y = Yes, indicating compatible requests; N = No, indicating incompatible requests;-indicating No lock request ):

Table 5: Compatibility Matrix of Oracle Database tmlock

On the one hand, when Oracle executes SELECT... For update, INSERT, UPDATE, DELETE, and other DML statements, the system automatically performs the RS lock (SELECT... For update) or RX lock (INSERT, UPDATE, DELETE). When the table-Level Lock is obtained, the system automatically applies FOR the TX lock, the LOCK flag position of the actually locked data row (pointing to the tx lock); on the other hand, the program or operator can also use the lock table statement to specify a type of tmlock. The following table summarizes the situation where tmlocks are generated by SQL statements in Oracle:

Table 6: Oracle Database tmlock Summary

We can see that the common DML operations (SELECT... For update, INSERT, UPDATE, DELETE), only intention locks (RS or RX) are obtained at the table level, and the true blocking granularity is at the row level. In addition, A notable feature of Oracle databases is that, by default, simply reading data (SELECT) is not locked, and Oracle uses Rollback segment) to prevent users from reading "dirty" data. These increase the concurrency of the system.
The introduction of intention locks and data row locks reduces the overhead for Oracle to maintain row-level locks. These applications enable Oracle to efficiently process highly concurrent transaction requests.
4 Monitoring of the Multi-granularity blocking mechanism of DB2
There are two ways to monitor locks in DB2: Snapshot monitoring and event monitoring.
4.1 snapshot Monitoring Method
Before using the snapshot method to monitor the lock, you must turn on the monitoring lock switch, which can be opened at the instance level and session level. The specific command is as follows:

Db2 update dbm cfg using dft_mon_lock on (instance level)
Db2 update monitor switches using lock on (session level, recommended)
When the switch is turned on, You can execute the following command to monitor the lock.
Db2 get snapshot for locks on ebankdb (details of the specific locks in the current database can be obtained)
Db2 get snapshot for locks on ebankdb
Fri Aug 15 15:26:00 JiNan 2004 (Red is the key information of the lock)


Database Lock Snapshot
Database name = DEV
Database path =/db2/DEV/db2dev/NODE0000/SQL00001/
Input database alias = DEV
Locks held = 49
Applications currently connected = 38
Agents currently waiting on locks = 6
Snapshot timestamp = 15:26:00. 951134
Application handle = 6
Application ID = * LOCAL. db2dev. 030815021007
Sequence number = 0001
Application name = disp + work
Authorization ID = SAPR3
Application status = UOW Waiting
Status change time =
Application code page = 819
Locks held = 0
Total wait time (MS) = 0
Application handle = 97
Application ID = * LOCAL. db2dev. 030815060819
Sequence number = 0001
Application name = tp
Authorization ID = SAPR3
Application status = Lock-wait
Status change time = 08-15-2003 15:08:20. 302352
Application code page = 819
Locks held = 6
Total wait time (MS) = 1060648
Subsection waiting for lock = 0
ID of agent holding lock = 100
Application ID holding lock = * LOCAL. db2dev. 030815061638
Node lock wait occurred on = 0
Lock object type = Row
Lock mode = Exclusive Lock (X)
Lock mode requested = Exclusive Lock (X)
Name of tablespace holding lock = PSAPBTABD
Schema of table holding lock = SAPR3
Name of table holding lock = TPLOGNAMES
Lock wait start timestamp = 15:08:20. 302356
Lock is a result of escalation = NO
List Of Locks
Lock Object Name = 29204
Node number lock is held at = 0
Object Type = Table
Tablespace Name = PSAPBTABD
Table Schema = SAPR3
Table Name = TPLOGNAMES
Mode = IX
Status = Granted
Lock Escalation = NO


Db2 get snapshot for database on dbname | grep-I locks (UNIX, LINUX)


Locks held currently = 7
Lock waits = 75
Time database waited on locks (MS) = 82302438
Lock list memory in use (Bytes) = 20016
Deadlocks detected = 0
Lock escalations = 8
Exclusive lock escalations = 8
Agents currently waiting on locks = 0
Lock Timeouts = 20

Db2 get snapshot for database on dbname | find/I "locks" (NT platform)
Db2 get snapshot for locks for applications agentid 45 (Note: 45 is the application handle)


Application handle = 45
Application ID = * LOCAL. db2dev. 030815021827
Sequence number = 0001
Application name = tp
Authorization ID = SAPR3
Application status = UOW Waiting
Status change time =
Application code page = 819
Locks held = 7
Total wait time (MS) = 0
List Of Locks
Lock Object Name = 1130185838
Node number lock is held at = 0
Object Type = Key Value
Tablespace Name = PSAPBTABD
Table Schema = SAPR3
Table Name = TPLOGNAMES
Mode = X
Status = Granted
Lock Escalation = NO
Lock Object Name = 14053937
Node number lock is held at = 0
Object Type = Row
Tablespace Name = PSAPBTABD
Table Schema = SAPR3
Table Name = TPLOGNAMES
Mode = X
Status = Granted
Lock Escalation = NO


You can also execute the following list functions (Note: Only commands can be run before DB2 V8, and table functions can be used after DB2 V8. We recommend that you use table functions for Lock monitoring)
Db2 select * from table (snapshot_lock ('dbname',-1) as locktable monitoring lock Information
Db2 select * from table (snapshot_lockwait ('dbname',-1) as lock_wait_table monitoring application lock wait Information
4.2 event monitoring methods:
When using the event monitor for Lock monitoring, you can only monitor deadlocks (deadlocks are generated because the transaction cannot be terminated due to lock request conflicts, and the request conflicts cannot be resolved within the transaction. Generally, two applications hold the lock required by each other, And do not release the existing lock if they do not get the lock they need. The specific steps are as follows:
Db2 create event monitor dlock for deadlocks with details write to file '$ HOME/dir'
Db2 set event monitor dlock state 1
Db2evmon-db dbname-evm dlock depends on the specific deadlock output (for example)


Deadlocked Connection...
Deadlock ID: 4
Particle no.: 1
Particle no. holding the lock: 2
Appl Id: G9B58B1E. D4EA. 08D387230817
Appl Seq number: 0336
Appl Id of connection holding the lock: G9B58B1E. D573.079237231003
Seq. no. of connection holding the lock: 0126
Lock wait start time: 06/08/2005 08:10:34. 219490
Lock Name: 0x000201350000030E0000000052
Lock Attributes: 0x00000000
Release Flags: 0x40000000
Lock Count: 1
Hold Count: 0
Current Mode: NS-Share (and Next Key Share)
Deadlock detection time: 06/08/2005 08:10:39. 828792
Table of lock waited on: ORDERS
Schema of lock waited on: DB2INST1
Tablespace of lock waited on: USERSPACE1
Type of lock: Row
Mode of lock: NS-Share (and Next Key Share)
Mode application requested on lock: X-Exclusive
Node lock occured on: 0
Lock object name: 782
Application Handle: 298
Deadlocked Statement:
Type: Dynamic
Operation: Execute
Section: 34
Creator: NULLID
Package: SYSSN300
Cursor: SQL _CURSN300C34
Cursor was blocking: FALSE
Text: update orders set totaltax = ?, TOTALSHIPPING = ?,
LOCKED = ?, TOTALTAXSHIPPING = ?, STATUS = ?, FIELD2 = ?, TIMEPLACED = ?,
FIELD3 = ?, CURRENCY = ?, SEQUENCE = ?, TOTALADJUSTMENT = ?, ORMORDER = ?,
SHIPASCOMPLETE = ?, PROVIDERORDERNUM = ?, TOTALPRODUCT = ?, DESCRIPTION = ?,
MEMBER_ID = ?, ORGENTITY_ID = ?, FIELD1 = ?, STOREENT_ID = ?, ORDCHNLTYP_ID = ?,
ADDRESS_ID = ?, LASTUPDATE = ?, COMMENTS = ?, NOTIFICATIONID =? WHERE ORDERS_ID =?
List of Locks:
Lock Name: 0x000201350000030E0000000052
Lock Attributes: 0x00000000
Release Flags: 0x40000000
Lock Count: 2
Hold Count: 0
Lock Object Name: 782
Object Type: Row
Tablespace Name: USERSPACE1
Table Schema: DB2INST1
Table Name: ORDERS
Mode: X-Exclusive
Lock Name: 0x00020040000029B30000000052
Lock Attributes: 0x00000020
Release Flags: 0x40000000
Lock Count: 1
Hold Count: 0
Lock Object Name: 10675
Object Type: Row
Tablespace Name: USERSPACE1
Table Schema: DB2INST1
Table Name: BKORDITEM
Mode: X-Exclusive (skip the following information)

5 Oracle multi-granularity blocking mechanism monitoring
To monitor the locks in the Oracle system, we need to understand several system views:
5.1 v $ lock View
The v $ lock view lists all the locks held by the current system or being applied. The main fields are described as follows:

Table 7: main fields in the v $ lock View

In the value of the TYPE field, this article only cares about the TM and tx dml lock types;
5.2 v $ locked_object View
The v $ locked_object view lists the objects in the current system that are being locked. The main fields are described as follows:

Table 8: field descriptions in the v $ locked_object View

5.3 Oracle lock monitoring script
Based on the above system view, you can compile a script to monitor the locks in the database.
5.3.1 showlock. SQL
The first script showlock. SQL, which connects the v $ locked_object and all_objects views to show which objects are locked by which sessions:


/* Showlock. SQL */
Column o_name format a10
Column lock_type format a20
Column object_name format a15
Select rpad (oracle_username, 10) o_name, session_id sid,
Decode (locked_mode, 0, 'none', 1, 'null', 2, 'row share ',
3, 'row Exclusive ', 4, 'share', 5, 'share Row Exclusive', 6, 'clusive ') lock_type,
Object_name, xidusn, xidslot, xidsqn
From v $ locked_object, all_objects
Where v $ locked_object.object_id = all_objects.object_id;
5.3.2 showalllock. SQL

The second script, showalllock. SQL, displays information about all current TM and TX locks;


/* Showalllock. SQL */
Select sid, type, id1, id2,
Decode (lmode, 0, 'none', 1, 'null', 2, 'row share ',
3, 'row Exclusive ', 4, 'share', 5, 'share Row Exclusive', 6, 'clusive ')
Lock_type, request, ctime, block
From v $ lock
Where type in ('tx ', 'Tm ');
6 DB2 multi-granularity blocking mechanism example
The following example runs in DB2 UDB and applies to all database versions. First, open the three command line windows (DB2 CLP), two of which are represented by SESS #1 and SESS #2) and connect to the database as the db2admin user, use the example table (employee) provided in the SAMPLE database; the other (represented by SESS #3 below) to connect to the database as a db2admin user, monitors the locking of each type of SQL statement executed. You are expected to monitor the locking of each type of SQL statement in this way. (Because the example is very long, I will not do it here. It is recommended that you use a similar method to verify the locking situation)


/Home/db2inst1> db2 + c update employee set comm = 9999 (SESS #1)
/Home/db2inst1> db2 + c select * from employee (SESS #2 is in lock wait)
/Home/db2inst1> db2 + c get snapshot for locks on sample (SESS #3 monitoring lock status)

Note: db2 + c does not automatically submit (commit) SQL statements. You can also use db2 update command options using c off to disable automatic commit (autocommit, which is automatically submitted by default)
7. Conclusion
In general, DB2 locks differ from Oracle locks in the following ways:
1. Oracle uses the multi-granularity blocking mechanism with intention locks to control concurrency to ensure data consistency. Its DML lock (Data lock) is divided into two levels (granularity): Table-level and row-level. Generally, DML operations only obtain the intention lock (RS or RX) at the table level, and the real blocking granularity is still at the row level; DB2 also uses a multi-granularity blocking mechanism with intention locks to control concurrency to ensure data consistency. Its DML lock (Data lock) is divided into two levels (granularity): Table-level and row-level. Generally, DML operations obtain only the intention lock (IS, SIX or IX) at the table level, and the real blocking granularity IS also at the row level. In addition, in the Oracle database, simply reading data (SELECT) is not locked, which improves the system's concurrency. Oracle emphasizes the ability to "read" data and quickly read data. DB2 locks emphasize "read consistency". When reading data (SELECT), values of S, IS are added based on different isolation levels (RR, RS, and CS, IS lock, which IS not locked only when UR isolation level IS used. This ensures that the data read by different applications and users is consistent.
2. while supporting high concurrency, DB2 and Oracle have different locking mechanisms: Oracle uses design techniques such as intention locks and lock marks on data rows, this reduces the cost of Oracle's Row-Level Lock maintenance and makes it advantageous in database concurrency control. In DB2, each lock will apply to allocate a certain byte of memory space in the lock memory (locklist), specifically the X lock 64 byte memory, S lock 32 byte memory (note: before DB2 V8, X locks 72 bytes of memory and S locks 36 bytes of memory ).
3. There is no lock upgrade in the Oracle database. When the row-Level Lock usage in the database table exceeds the locklist * maxlocks In the DB2 database, the lock upgrade will occur.
4. in Oracle, when a session is used to insert, update, and delete a table, another session can still read the table's front image (before image) from the mongoe rollback segment or restore the tablespace ); in DB2, when a session is used to insert, update, or delete a table, the other session is still in the lock wait status when reading the table data, unless the UR isolation level is used, the uncommitted values of the first session can be read. Therefore, different sessions in Oracle have read inconsistencies at the same time, all sessions of DB2 at the same time are read-consistent.
8 conclusion
Suggestions on concurrency control (LOCK) in DB2
1. properly adjust locklist, maxlocks, dlchktime, locktimeout, and other database configuration parameters related to the lock (locktimeout should not be equal to-1 ). If the lock memory is insufficient, the SQL0912 error will be reported, affecting the concurrency.
2. Write efficient and concise SQL statements (very important ).
3. Release the lock as quickly as possible after the business logic is processed.
4. create the most appropriate index for SQL statements that cause lock wait (SQL0911 return code 68) and deadlock (SQL0911 return code 2) (very important, try to create a composite index and include index ).
5. Use the LOCKSIZE parameter of the altER TABLE statement to control how to lock a specific TABLE on a persistent basis. Check the locksize field in syscat. tables. If possible, this field is "R" (Row-Level Lock) in each table in line with the business logic ).
6. Use the correct isolation level (RR, RS, CS, and UR) based on the business logic ).
7. When a large number of updates are executed, the entire TABLE is locked during the entire transaction before the update (using the SQL LOCK TABLE statement ). This only uses a lock to prevent other transactions from performing these updates, but it does reduce data concurrency for other users.