Databases Reference
In-Depth Information
BEGIN TRANSACTION
UPDATE account
SET balance
T
1
}
}
Tra wa
1.1
(
),
(
),
=
balance
+
100
1
987
1
987
WHERE accno
=
987
UPDATE account
SET balance 100 100
WHERE accno 123
COMMIT TRANSACTION
=-
=
Tra wa
1.2
(
),
(
)
1
123
1
123
T
1
(a)
BEGIN TRANSACTION
DECLARE @X INT, @Y INT
T
2
}
}
SELECT @X
=
FROM account
WHERE accno
SUM(balance)
Tr ar ar a
2.1
(
),
(
),
(
),
2
100
2
123
2
130
<
400
SELECT @Y SUM(Balance)
FROM account
WHERE accno
=
Tr ar ar ar a
2.2
(
),
(
),
(
),
(
)
2
400
2
563
2
588
2
987
>=
400
SELECT @X @Y
COMMIT TRANSACTION
+
T
2
(b)
Figure 9.6
Sample SQL transactions: (a) T
1
: transfer $100 to account 987 from account
123 and (b) T
2
: find total credit/debit of all customers.
the data on which the locks operate. However, this clearly will lead to delays
in transaction execution, because all locks need to be secured at all sites. To
avoid this, conflicts should involve only write locks, and a conflict needs to
be detected at only one site for the transaction to be prevented from execut-
ing incorrectly. Thus, in a DDB with n sites, assuming all sites are fault free,
it is required only that one site detects either of the following conflicts:
A read-write conflict. Suppose we have T
1
reading from o and T
2
writing to o, thus making T
1
and T
2
conflict. If we have placed read
locks for object o in transaction T
1
·
at k sites, then the number j of
write locks for o in transaction T
2
must exceed n
-
k
+
1 (i.e., j
³
n
1) to guarantee at least one site with both a read lock and a
write lock for o and thus detect the conflict.
-
k
+
